Array Help

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am relatively new to VBA programming but not VB. For my current
project I need to copy a cell to an array and paste it into a
spreadsheet only if a certain criteria is met, such as the number 3 in
column 9. Which corresponds to a drop down box. I would then need to
paste that cell into the first
pre-determined non occupied cell on a different worksheet. I would
like to do this using an array since it makes the most sense. I am
thinking one array for the copied cells and a second array that has
cell references on the second sheet. My hang up is the first array.
How do I only copy certains cells into an array if a certain criteria
must be met.

Thanks in advance,
John
 
Maybe something like this:

Dim varr(1 to 10) as Variant
Dim i as long
Dim varr1 as Variant

Varr1 = Array("A1","B9","C11","D12","Z1", _
"Z14","B2","A13","B15","C21")
i = 1
for each cell in Range("A1").Resize(10,1)
if cell.offset(0,5).value = 9
varr(i) = cell.value
i = i + 1
End if
Next

for i = 1 to 10
if not isempty(varr(i)) then
Worksheets(2).Range(varr1(i-1)).Value = varr(i)
end if
Next
 
Back
Top