Forum Discussion
Solved
Recorded Macro Variable in concatenate
I'm new to using macros and have attempted to make this one to save me some work. It is supposed to copy a sheet "Template", concatenate some data from another sheet and then use this to rename the sheet. Most of this is working as expected so I tried to add a loop and variable, which unfortunately hasn't worked.
The problem I have is that I can't repeat this and incrementally move through the list of names to concatenate as I can't put the variable "i" into the concatenate function.
Clearly I'm not doing this correctly, can someone please recommend a solution?
Here is the vba:
Dim i As Integer
i = -7
Do While i < 30
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(3)
Range("C9:F9").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE('Student Data'!R[i]C[-1],"" "",'Student Data'!R[i]C)"
Sheets("Template (2)").Select
Sheets("Template (2)").Name = Range("C9")
Range("I1") = i
i = i + 1
Loop
You can put the variable i in the function, but you have to build it. So a short example:
Instead of "=CONCATENATE('Student Data'!R[i]C[-1]" for the first part of your formula do this
"=CONCATENATE('Studet Data'!R[" & cstr(i) & "]C[-1]"
That will convert the variable i into a string (cstr() is convert to string) then build the function dynamically. So if i = 1 it becomes
=CONCATENATE('Student Data'!R[1]C[-1]Then when i = 2 it is
=CONCATENATE('Student Data'!R[2]C[-1]Etc.
You can put the variable i in the function, but you have to build it. So a short example:
Instead of "=CONCATENATE('Student Data'!R[i]C[-1]" for the first part of your formula do this
"=CONCATENATE('Studet Data'!R[" & cstr(i) & "]C[-1]"
That will convert the variable i into a string (cstr() is convert to string) then build the function dynamically. So if i = 1 it becomes
=CONCATENATE('Student Data'!R[1]C[-1]Then when i = 2 it is
=CONCATENATE('Student Data'!R[2]C[-1]Etc.
Thanks Ed! I really appreciate the help.
