Text in formula

[diepvic]

I need to assign a formula for cell A1 as below:
Range("A1") .formula = "=Match(""USD"", A:A, 0)"

However, the lookup_value (USD) is a variable, named Ccy (currency). How
should I change the code above to replace the USD with the variable Ccy?

Many thanks!

 

[Gary Keramidas]

try this

Range("A1").Formula = "=Match(" & ccy & ", A:A, 0)"

 

[OssieMac]

Not sure I understand the question when you use USD in the formula then say
it is a variable called Ccy. However, try the following and see if it is what
you want.

Note that when you insert a formula like that it is simply a string so
variables can be inserted simply by concatenating them with the remainder of
the formula by closing the double quotes, add an ampersand and then the
variable and another ampersand then open the double quotes again.

Range("A1").Formula = "=Match(" & Ccy & ", A:A, 0)"

 

[diepvic]

Thx,
The problem is: the formula assigned to A1 would be: Match(USD,B:B,0)
whereas the correct one should be Match("USD",B:B,0)

 

[Rick Rothstein]

Try this then...

Range("A1").Formula = "=Match(""" & Ccy & """, A:A, 0)"