vba - excel - Loop through worksheet -

i have never used marcos in excel before in access. want loop through worksheet , column x , y not null extract information new worksheet. row y may have more 1 value , create new record each value in y x. each value separated comma.

the result is

a c3

a c4

b c6

b c7

b c10

and on

here code far:

sub extract() ' ' extract macro ' dim wksht worksheet, myothersheet worksheet, mybook workbook dim r integer dim regex dim match dim text set mybook = excel.activeworkbook set myothersheet = mybook.sheets("new") set regex = createobject("vbscript.regexp") regex.patten = """[^""]*""|[^,]*" regex.global = true     j = 0 each wksht in thisworkbook.worksheets     if wksht.name = "sheet1"         r = 1 1000           if wksht.rows(r, b).value <> "" & wksht.rows(r, g).value <> ""           text = wksht.rows(r, g).value            each match in regex.execute(text)            myothersheet.cells(j, 1).value = wksht.cells(r, b)            myothersheet.cells(j, 2).value = match            j = j + 1            next match            exit            end if            r = r + 1            next r            exit            end if            next wksht    end sub 

im unable run, think syntax wrong regex, iv ever used in c#, best option trying achieve, appreciated ?

to fix 1004 error, change

rows(r, b).value

to

range(r, b).value

the rows property takes single index, trying test cell, muct use either range or cell object.

also make programming regex easier, add reference microsoft vbscript regular expressions 5.5 library:

on tools>references menu in vb editor.

then can declare dim regex regexp , use set regex = new regexp

this give intellisense code corerctly.

for tutorial in using regular expressions excel vba, see

• macrostash: regular expression tutorial excel vba
• regular expressions website

also, in tools>options editor tab, check boxes require variable declaration , auto list members