Concatenate macro Type Mismatch error

Newbie to macros and VBA here. I am attempting to concatenate several columns and I am getting the Type Mismatch error. I understand why I am getting this error, but I do not know any other way to accomplish what I am seeking. Any help would be greatly appreciated.

Please Login or Register  to view this content.

Thanks in advance,
Ray

I think you forgot your double quotes.

Please Login or Register  to view this content.

Hi - you are trying to use A1 style references for the R1C1 formula, you either need to change formular1c1 to formula or change the references from A1 to R1C1.

You also need to double up quote marks. I would suggest:

Please Login or Register  to view this content.

Thank you fellas, they all worked. But it brought up another issue. I would like this macro to:
1. Insert the concatenate formula only if cell AA is blank. This is a cumlative monthly file and I dont want to redo previous months, only perform on newly added data.
2. If column R is blank, I would like the macro to skip that row entirely.

Is this possible?

Thanks in advance,
Ray

If you copy the code in post 1 into the vba editor it will add the spaces, I guess it segragates the parts in quotation marks (i.e. "=concatenate(A1," from the dashes and spaces them apart from the "-" which it is reading as subtraction signs.

You get the same thing if you type:
a=1+2
the VBA editor will adjust it to
a = 1 + 2

I hadn't noticed the change when copying back to the post.

Originally Posted by Kaper

Hi John and ragulduy,

Is there any reason why you both added spaces at both sides of dash:

Please Login or Register  to view this content.

while in original there was only:

Please Login or Register  to view this content.

of course double quotes are needed (I added them too) but why spaces around?

No. The extra spaces were added when I pasted the code from the thread.

Issue Solved. Even though its way above my level of understanding, it worked great Kaper!

Thank you very much Kaper, JHD and Ragulduy for your help.

Thanks,
Ray