SOLVED
Home

Recorded Macro Variable in concatenate

Highlighted
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
Highlighted
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.

Highlighted

Thanks Ed! I really appreciate the help.

Related Conversations
Excel Table Size in Macro
WayneEK in Excel on
6 Replies
Macro issue with email connectivity
unhappyuser in Excel on
21 Replies
New Spill function making concatenate output difficult?
nickcicc in Excel on
6 Replies
Marco to create .CSV
DooDahMan in Excel on
0 Replies
vlookup using concatenate function and helper column
Angela McGhin in Excel on
4 Replies
Simple Text Searching Macro
docbrain in Excel on
0 Replies