variable references to worksheets when looping

[Ashleigh]

Hello All,

I am encountering some problems with a macro designed to essentially
create an indirect reference based on the contents of certain cells. I
have set the macro up to loop (using variable i). Below is the code I
am working with.

Sub Macro4()

For i = 72 To 9 Step -1

Dim s1 As string
s1 = Cells(i, 2).Value
‘2nd column (B) contains worksheet tab name

Dim s2 As string
s2 = "G51"
‘G51 is the cell I would like to reference on the worksheet name
(found in column 2)

Dim s3 As Variant

S3 = s1 & "!" & s2
‘here I am trying to create a reference to that cell…ordinarily I
would use the indirect function, but I don't think you can use this in
vb.

Cells(i, 6).Value = Range(s3)
'I would like the value of [tab name]!G51 to be returned to this cell
Next i
End Sub


Maybe I am making this more complicated than it needs to be, but I
would appreciate feedback on how to correct this macro. Thanks in
advance!

A

 

[Frank Kabel]

Hi
not quite sure I understood you correctly but try (not fully tested)

Sub Macro4()
Dim wks as worksheet
Dim rng as range
Dim s1 As string

For i = 72 To 9 Step -1
s1 = Cells(i, 2).Value
'2nd column (B) contains worksheet tab name

set wks = worksheets(s1)
set rng = wks.range("G51")

Cells(i, 6).Value = ´rng.value
Next i
End Sub