excel - Create Sub array variable name sheet -

i have 1 array sheet names called sheetnames , want generate sub array of returns true @ condition (if). try have loop cell value onto different sheets, evaluating condition cell.value = "s". when checks first d column (z = 4) want make same check (if condition) columns d dr @ same row.

i need similar result if use formula @

diary!c7    = if (element!d6 = "s",concatenate (element!b1, ", "), ""),    if (element1!d6 = "s",concatenate (element1!b1, ", "), ""), ....    if (element!e6 = "s",concatenate (element!b1, ", "), ""),    if (element1!e6 = "s",concatenate (element1!b1, ", "), "") .... ) 

where element sheet name taken array sheet names condition (code s or code).

sheetnames 1 array book sheets , fsheet (filtered sheet condition) array filtered (with condition if). when can populate fsheet array each sheet test condition must concatenate it's values @ sheet/cell , began test condition again next cell (e6) ... i'm trapped @ step create fsheet.

        sub test()         dim ws worksheet         dim sheetnames() string, fsheets() string, q string         dim element variant         dim lastsheet integer, r integer, incrsheet integer, integer         dim rgn range      ' enter sheet names array. redim array's size number of sheets (lastsheet)       each ws in activeworkbook.worksheets         redim preserve sheetnames(lastsheet)         sheetnames(lastsheet) = ws.name         lastsheet = lastsheet + 1        next ws     msgbox lastsheet     ' test condition each sheet/cell     z = 4 11         each element in sheetnames()             incrsheet = 1             if activeworkbook.sheets(element).cells(6, z).value = "s"                  redim preserve fsheets(incrsheet)                  fsheets(incrsheet) = element                  incrsheet = incrsheet + 1             end if         next element     next z     = 3 ' define sheet work (total project have more one, 1 code need test, s, c, etc)  worksheets("diary")         .activate         .range("c7").select  ' concatenate values @ summary page                cells(7, i).select             r = 1 ubound(fsheets)             'concatenate &:                varconctnt = varconctnt & ", " & fsheets(r)                next r             'remove "&" before first element:             varconctnt = mid(varconctnt, 2)             q = varconctnt             varconctnt = ""         = + 1         activecell.value = q     loop while < 11 ' drag formula rest of rows range("c7:j7").select         selection.autofill destination:=range("c7:j12"), type:=xlfilldefault     end     end sub 

where going wrong, attempt dynamically set range. assuming testing value of single cell, easier use cells, rather range, since can use r1c1 notation. try this:

incrsheet = 1  z = 4 11     each element in sheetnames()         if activeworkbook.sheets(element).cells(6, z).value = "s"              redim preserve fsheets(incrsheet)              fsheets(incrsheet) = element              msgbox incrsheet              incrsheet = incrsheet + 1         end if     next element next z