indirect doesn't work in vba

blue333

Board Regular
Joined
Mar 19, 2009
Messages
64
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
And what is the ... ?

Please can you show us the actual code that is failing?
 
Upvote 0
And what is the ... ?

Please can you show us the actual code that is failing?

Here you go. The msgbox commands are for debugging purposes only to see what is being reported. The creation of the CopyRangeArray() entries is actually done elsewhere in the code but i reproduced it below. The array is being correctly filled so that is not the problem.:

Dim CopyRangeArray() As String
MaxLoopIterations = WorksheetFunction.CountIf(Sheet3.Columns(1), "yes")
ReDim CopyRangeArray(1 To MaxLoopIterations)

For Counter = 1 To MaxLoopIterations

'...misc code

Set FoundCell = Sheet3.Columns(1).Find(what:="yes", After:=FoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

CopyRangeArray(Counter) = Cells(FoundCell.Row, PrimKeyPosition).Address & ":" & _
Cells(FoundCell.Row + Sheet1.Range("extract_value").Value - 1, LastColumn).Address

'...misc code

next counter

Below is the output of the array and they correctly identify the correct ranges that need to be copied:

$F$2:$L$13
$F$47:$L$58
$F$300:$L$311
$F$505:$L$516
$F$550:$L$561
$F$803:$L$814
$F$1008:$L$1019
$F$1053:$L$1064
$F$1306:$L$1317

The problem starts here. The following code is used to copy ranges that meet certain criteria. those ranges are created in CopyRangeArray() from above.

For Counter = 1 To MaxLoopIterations

MsgBox "before stuff.."

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

MsgBox "Right = " '& Sheet3(CopyRangeArray(Counter)).Value

MsgBox "addresses = " & CopyRangeArray(Counter)

MsgBox "Left = " '& Worksheets(NewWSName).Cells(CopyRangeArray(Counter)).Value

Next Counter

msgbox "done"

The puzzling thing is that the ONLY msgbox that appear are the ones for "before stuff...", "done" and "address". The remaining two msgbox do not even appear. That is strange. They don't even show up! Wierd. If i comment out the part after & then the msgbox will appear. I don't know why this is happening.

Hope this helps and thanks for your reply.
 
Last edited:
Upvote 0
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
 
Upvote 0
Do you have an enabled error handler (some kind of a On Error...statement?)

The Sheet3.Range(...).value should work. You only indicate "No it doesn't work" but not how it fails. That would help.
Here you go. The msgbox commands are for debugging purposes only to see what is being reported. The creation of the CopyRangeArray() entries is actually done elsewhere in the code but i reproduced it below. The array is being correctly filled so that is not the problem.:

Dim CopyRangeArray() As String
MaxLoopIterations = WorksheetFunction.CountIf(Sheet3.Columns(1), "yes")
ReDim CopyRangeArray(1 To MaxLoopIterations)

For Counter = 1 To MaxLoopIterations

'...misc code

Set FoundCell = Sheet3.Columns(1).Find(what:="yes", After:=FoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

CopyRangeArray(Counter) = Cells(FoundCell.Row, PrimKeyPosition).Address & ":" & _
Cells(FoundCell.Row + Sheet1.Range("extract_value").Value - 1, LastColumn).Address

'...misc code

next counter

Below is the output of the array and they correctly identify the correct ranges that need to be copied:

$F$2:$L$13
$F$47:$L$58
$F$300:$L$311
$F$505:$L$516
$F$550:$L$561
$F$803:$L$814
$F$1008:$L$1019
$F$1053:$L$1064
$F$1306:$L$1317

The problem starts here. The following code is used to copy ranges that meet certain criteria. those ranges are created in CopyRangeArray() from above.

For Counter = 1 To MaxLoopIterations

MsgBox "before stuff.."

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

MsgBox "Right = " '& Sheet3(CopyRangeArray(Counter)).Value

MsgBox "addresses = " & CopyRangeArray(Counter)

MsgBox "Left = " '& Worksheets(NewWSName).Cells(CopyRangeArray(Counter)).Value

Next Counter

msgbox "done"

The puzzling thing is that the ONLY msgbox that appear are the ones for "before stuff...", "done" and "address". The remaining two msgbox do not even appear. That is strange. They don't even show up! Wierd. If i comment out the part after & then the msgbox will appear. I don't know why this is happening.

Hope this helps and thanks for your reply.
 
Upvote 0
Do you have an enabled error handler (some kind of a On Error...statement?)

The Sheet3.Range(...).value should work. You only indicate "No it doesn't work" but not how it fails. That would help.

it works now!!!

thanks for everyone for their help. i really appreciate!!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top