quotes problem using variable in concatenate function R1C1 notation

[carpking]

Hi all,

Please help ... been stuck all day with this issue

the following vba code

ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-1],"" ""," & supplier & ")"

generates

=CONCATENATE(A2," ",'C0267') instead of =CONCATENATE(A2," ",C0267)

There must be something small wrong with the notation, any ideas?

Thanks

 

[Richard Schollar]

Is it just this:

ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-1],"" "",'" & supplier & "')"

I have included apostrophes both before and after your variable name (inside the double quotes).


EDIT: Oops, misread...

 

[Norie]

If you are using FormulaR1C1 then all the references need to be in R1C1 notation.

Is C0267 meant to be a cell reference?

 

[p45cal]

perhaps:
"=CONCATENATE(RC[-1],"" "",R[" & supplier & "]C)"

 

[RoryA]

Or did you mean:

ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-1],"" "",""" & supplier & """)"

 

[carpking]

Hi All,

Thankyou for your replies, each one is much appreciated. Rorya, your solution has achieved what I need, thankyou very much.

To clarify for everyone, I am trying to generate a concatenation function inside a cell that joins a cell reference, a space and a variable's value. This requires some deft footwork with the quotes in the vba since the required function within the cell must be ...

=CONCATENATE(A2, " ", "C0267") and the C0267 is the value stored in the supplier variable. Usage of the

'" & supplier & "'

and other permutations either left the cell blank or created an error.

I hope that helps anyone else with this requirement. Once again, thankyou all.