VBA is it possible to use a integer like function with cells

[Jonahsav]

hey,

so im really new to VBA, and im not sure if this is possible.

the thing i want to achieve is:

in row a i have names, these are names of sheets i already have. these sheets change.
so lets say A1 has "sheet1" in it. now what i want is that with VBA i want to make a formula that takes the text of A1 and puts it in B1 with a "=" etc.
so the end result should be something along the line with B1= "=sheet1!A1"
i know this can be done with:
sProfileName = Range("a1").Value
Range(B1).Formula = "=" & sProfileName & "!A1"

but now i want to somewhat continue. so that it automatically does the same with A2, A3, A4 etc.
so in A2 there's "sheet2" and the VBA puts "=sheet2!A1" in B2
and in A3 there's "sheet3" and the VBA puts "=sheet3!A1" in B3

i know with integer (maybe this had nothing to do with it) you can say i = 1 to 10, and the first i takes 1 second takes 2 etc. is it possible to say something along the line of integer i = range A1 to A3 and then in the first i it uses the text of A1 (so "sheet1") and the second time i gets used it takes the text of a2 (so "sheet2").

i have no idea if this is understandable haha..
let me know if its not,

thanks in advance Jonah

[yujin]

Hi, Jonah

If I understand your post correctly, you can get the result you want with the code like below.

Sub Macro1()    Dim i As Integer
    Dim sProfileName As String
    
    For i = 1 To 10
        sProfileName = Range("A" & i).Value
        Range("B" & i).Formula = "=" & sProfileName & "!A1"
    Next
End Sub

Furthermore, you can get the same result by using the INDIRECT function.
Type "=INDIRECT(A1&"!A1")" into B1 and copy it down to B10.