indirect doesn't work in vba

[blue333]

hi

i have an array of strings that holds addresses of cell references. I originally tried

Dim CopyRangeArray() As String
ReDim CopyRangeArray(1 To MaxEntries)

for counter=1 to 100
... = Sheet3(CopyRangeArray(Counter)).Value
next counter

but it doesnt work. I can do this text to address conversion using INDIRECT in non-vba but not vba.

any suggestions?

thanks

 

[blue333]

If the array contains strings of range addresses like
$F$2:$L$13

Then when you use those to copy from, you must use the RANGE object, not CELLS.

The Cells object requires Row # and Column # or letter, like
Cells(row#,Column# or letter)
Cells(1,1) = Range("A1")
Cells(1,"A") = Range("A1")

You are using
Cells($F$2:$L$13)
That is invalid syntax.

Worksheets(NewWSName).Cells(CopyRangeArray(Counter)).Value = Sheet3(CopyRangeArray(Counter)).Value

Translated applying your array variable
Worksheets(NewWSName).Cells($F$2:$L$1).Value = Sheet3($F$2:$L$1).Value

Plus, you didn't even use Range OR Cells on the 2nd half.


It should be like this

Worksheets(NewWSName).Range(CopyRangeArray(Counter)).Value = Sheet3.Range(CopyRangeArray(Counter)).Value

thanks jonmo1. it works now. i appreciate your help!!!