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

 

[Fluff]

How about

Sub Myloop()
   Dim Cl As Range
   
   For Each Cl In Range("A1", Range("A" & Rows.count).End(xlUp))
      Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1"
   Next Cl
End Sub

 

[jonahsav]

ur a hero, thanks alot
i have no idea how it works, but it works

thanks again

 

[Fluff]

Glad to help & thanks for the feedback

All it's doing is looping the col A from A1 to the last used cell in that column & adding the cell value to the formula in colB

 

[jonahsav]

thanks again , theres only one small problem. it works great but colom a is filled with formula's in with like a if function that says if > then "sheet1"if not then """, so it works great and does it well but at the end of the macro i get an error because it cant find sheet "".

lol this is a vague explanation.

it works great but get an error at the end, is this fixable?

thankssss

 

[Fluff]

How about

Sub Myloop()
   Dim Cl As Range
   
   For Each Cl In Range("A1", Range("A" & Rows.count).End(xlUp))
      If Cl.Value = "" Then Exit For
      Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1"
   Next Cl
End Sub

 

[Rick Rothstein]

How about

Sub Myloop()
   Dim Cl As Range
   
   For Each Cl In Range("A1", Range("A" & Rows.count).End(xlUp))
      If Cl.Value = "" Then Exit For
      Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1"
   Next Cl
End Sub

I don't think you should exit the For loop, rather, I think if the cell in Column A is displaying the empty text string (""), you should just skip over it (there might be other cells after it with displayed sheet names). Something like this for your code...

[table="width: 500"]
[tr]
	[td]Sub Myloop()
   Dim Cl As Range
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If Len(Cl.Value) Then Cl.Offset(, 1).Formula = "=" & Cl.Value & "!A1"
   Next Cl
End Sub[/td]
[/tr]
[/table]

And, for those who find such things interesting, you can also do this without using a loop...

[table="width: 500"]
[tr]
	[td]Sub Myloop2()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Formula = Evaluate(Replace("IF(@="""","""",""=""&@&""!A1"")", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[jonahsav]

they both work, rick thanks for your response in my case its possible to end the loop.

so both macros work perfect, thanks alot