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!

try this

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

--


Gary Keramidas
Excel 2003


"diepvic" wrote in message
...
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!

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)"

--
Regards,

OssieMac


"diepvic" wrote:

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!

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)

"OssieMac" wrote:

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)"

--
Regards,

OssieMac


"diepvic" wrote:

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!

Try this then...

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

--
Rick (MVP - Excel)


"diepvic" wrote in message
...
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)

"OssieMac" wrote:

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)"

--
Regards,

OssieMac


"diepvic" wrote:

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!