Format for Concatenate formula which includes single quotes

Greetings All

I have a formula that I wanted to include in a VBA procedure but it contains a single quote addressing a different tab in the active workbook.

Please Login or Register  to view this content.

This is the formula as it sits in the cell, but everything I try to do keeps hanging up. I have tried declaring 'Data Staging'!$A$3 as a variable and using it that way as well, but if that should work, I haven't been able to come up with the correct pattern of quotation marks.

I would appreciate any help!

Thanks

Will this work? I know VBA wants a space on each side of the &.

Formula:

Please Login or Register  to view this content.

Originally Posted by Jacc

Will this work? I know VBA wants a space on each side of the &.

Formula:

Please Login or Register  to view this content.

I tried this and I end up with the formula with text refDate instead of the value of refDate. I then went back and checked the value of refDate and it is correct.

I think I misunderstood the problem. I thought you wanted VBA to put this formula in a cell for you, maybe that is wrong?
Could you post a sample workbook?

I found the problem was in the formatting part. Perhaps that was obvious to everyone but me? Anyway, disregard my local dateformat, the quotemarks are correct here. This works for me.

Formula:

Please Login or Register  to view this content.

Originally Posted by Jacc

I found the problem was in the formatting part. Perhaps that was obvious to everyone but me? Anyway, disregard my local dateformat, the quotemarks are correct here. This works for me.

Formula:

Please Login or Register  to view this content.

I don't know why it isn't working for me, but copying your code and pasting it into my macro (then changing the date format) still has it picking up my variable name (refDate) as text.

Originally Posted by jacob@thepenpoint

I don't know why it isn't working for me, but copying your code and pasting it into my macro (then changing the date format) still has it picking up my variable name (refDate) as text.

It is all in the formatting. I finally played around with the quotation marks and located the correct sequence. In case anyone else needs this solution:

Please Login or Register  to view this content.

note there are 3 double quotation marks before and after the variable name. Works like a champ! Thanks for the help, I had nearly given up on this one.