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

jonahsav

New Member
Joined
Aug 18, 2018
Messages
8
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
How about
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
How about
Code:
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
 
Upvote 0
How about
Code:
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...
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...
Code:
[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]
 
Last edited:
Upvote 0
they both work, rick thanks for your response in my case its possible to end the loop.

so both macros work perfect, thanks alot :)
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

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