SOLVED

Recorded Macro Variable in concatenate

%3CLINGO-SUB%20id%3D%22lingo-sub-352128%22%20slang%3D%22en-US%22%3ERecorded%20Macro%20Variable%20in%20concatenate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352128%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20new%20to%20using%20macros%20and%20have%20attempted%20to%20make%20this%20one%20to%20save%20me%20some%20work.%20It%20is%20supposed%20to%20copy%20a%20sheet%20%22Template%22%2C%20concatenate%20some%20data%20from%20another%20sheet%20and%20then%20use%20this%20to%20rename%20the%20sheet.%20Most%20of%20this%20is%20working%20as%20expected%20so%20I%20tried%20to%20add%20a%20loop%20and%20variable%2C%20which%20unfortunately%20hasn't%20worked.%3C%2FP%3E%3CP%3EThe%20problem%20I%20have%20is%20that%20I%20can't%20repeat%20this%20and%20incrementally%20move%20through%20the%20list%20of%20names%20to%20concatenate%20as%20I%20can't%20put%20the%20variable%20%22i%22%20into%20the%20concatenate%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClearly%20I'm%20not%20doing%20this%20correctly%2C%20can%20someone%20please%20recommend%20a%20solution%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20vba%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20i%20As%20Integer%3CBR%20%2F%3Ei%20%3D%20-7%3C%2FP%3E%3CP%3EDo%20While%20i%20%26lt%3B%2030%3CBR%20%2F%3ESheets(%22Template%22).Select%3CBR%20%2F%3ESheets(%22Template%22).Copy%20After%3A%3DSheets(3)%3CBR%20%2F%3ERange(%22C9%3AF9%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20_%3CBR%20%2F%3E%22%3DCONCATENATE('Student%20Data'!R%5Bi%5DC%5B-1%5D%2C%22%22%20%22%22%2C'Student%20Data'!R%5Bi%5DC)%22%3CBR%20%2F%3ESheets(%22Template%20(2)%22).Select%3CBR%20%2F%3ESheets(%22Template%20(2)%22).Name%20%3D%20Range(%22C9%22)%3CBR%20%2F%3ERange(%22I1%22)%20%3D%20i%3CBR%20%2F%3Ei%20%3D%20i%20%2B%201%3CBR%20%2F%3ELoop%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-352128%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352282%22%20slang%3D%22en-US%22%3ERe%3A%20Recorded%20Macro%20Variable%20in%20concatenate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352282%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Ed!%20I%20really%20appreciate%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352222%22%20slang%3D%22en-US%22%3ERe%3A%20Recorded%20Macro%20Variable%20in%20concatenate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352222%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20put%20the%20variable%20i%20in%20the%20function%2C%20but%20you%20have%20to%20build%20it.%20So%20a%20short%20example%3A%3C%2FP%3E%3CP%3EInstead%20of%20%3CSTRONG%3E%22%3D%3C%2FSTRONG%3E%3CSPAN%3E%3CSTRONG%3ECONCATENATE('Student%20Data'!R%5Bi%5DC%5B-1%5D%22%3C%2FSTRONG%3E%20for%20the%20first%20part%20of%20your%20formula%20do%20this%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22%3DCONCATENATE('Studet%20Data'!R%5B%22%20%26amp%3B%20cstr(i)%20%26amp%3B%20%22%5DC%5B-1%5D%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThat%20will%20convert%20the%20variable%20i%20into%20a%20string%20(cstr()%20is%20convert%20to%20string)%20then%20build%20the%20function%26nbsp%3Bdynamically.%20So%20if%20i%20%3D%201%20it%20becomes%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DCONCATENATE('Student%20Data'!R%5B1%5DC%5B-1%5D%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%3EThen%20when%20i%20%3D%202%20it%20is%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DCONCATENATE('Student%20Data'!R%5B2%5DC%5B-1%5D%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3EEtc.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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.