SOLVED
Home

Recorded Macro Variable in concatenate

MrZacMatthews
New Contributor

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

2 Replies
Solution

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.

Related Conversations
New Spill function making concatenate output difficult?
nickcicc in Excel on
2 Replies
vlookup using concatenate function and helper column
Angela McGhin in Excel on
4 Replies
Macro issue with email connectivity
unhappyuser in Excel on
21 Replies
Excel Table Size in Macro
WayneEK in Excel on
6 Replies
Save as PDF button
Connor Smith in Excel on
1 Replies