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

excel

Formulas and Functions

Macros and VBA

office 365

Ed Hansberry
Feb 17, 2019
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.

Ed Hansberry

Iron Contributor

Feb 17, 2019

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.

MrZacMatthews
Copper Contributor
Feb 17, 2019
Thanks Ed! I really appreciate the help.

Forum Discussion

Recorded Macro Variable in concatenate

Resources