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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies