Variable in a Formula
 

Hello all. I'm having a problem trying to place a variable in a
formula. I'm using Excel 2000. I am using the Exact formula to
compare two text strings. Both strings are variables, and one string
needs quotes around it for Excel to evaluate the formula properly.
The quotes are where I'm hanging up. Here is an example of my code:

AnVar1 = "" & AnVar & ""
Range("IV1").Formula = "=EXACT(" & AnVar1 & "," & ActiveCell.Address &
")"

AnVar is something like Ag, or Al, etc. AnVar1 sets the string
correctly so it reads: "Ag" or "Al". When this is passed to the
formula the quotes are not included. So, in Excel in cell IV1 the
formula is: =Exact(Ag,"B1"). Since there no quotes around the Ag text
the formula errors out.

I've tried various ways to get the quotes included like using double
quotes, plus signs, and & signs. These usually produce either errors
or passes straight variable text to the formula.

I would appreciate any help on this problem.

Thanks,
Amy

Variable in a Formula
 

Amy,

You need to enclose the value of AnVar1 within quotes, and when
you need to use literal quotes in a VBA string, you use two of
the "". So your code would look like the following:

Range("IV1").Formula = "=EXACT(""" & AnVar1 & """," & _
ActiveCell.Address & ")"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Amy Ja" wrote in message
om...
Hello all. I'm having a problem trying to place a variable in
a
formula. I'm using Excel 2000. I am using the Exact formula
to
compare two text strings. Both strings are variables, and one
string
needs quotes around it for Excel to evaluate the formula
properly.
The quotes are where I'm hanging up. Here is an example of my
code:

AnVar1 = "" & AnVar & ""
Range("IV1").Formula = "=EXACT(" & AnVar1 & "," &
ActiveCell.Address &
")"

AnVar is something like Ag, or Al, etc. AnVar1 sets the string
correctly so it reads: "Ag" or "Al". When this is passed to
the
formula the quotes are not included. So, in Excel in cell IV1
the
formula is: =Exact(Ag,"B1"). Since there no quotes around the
Ag text
the formula errors out.

I've tried various ways to get the quotes included like using
double
quotes, plus signs, and & signs. These usually produce either
errors
or passes straight variable text to the formula.

I would appreciate any help on this problem.

Thanks,
Amy

Variable in a Formula
 

Hi
try
Range("IV1").Formula = "=EXACT(" & "" & AnVar1 & "" & "," &
ActiveCell.Address &")"


--
Regards
Frank Kabel
Frankfurt, Germany

"Amy Ja" schrieb im Newsbeitrag
om...
Hello all. I'm having a problem trying to place a variable in a
formula. I'm using Excel 2000. I am using the Exact formula to
compare two text strings. Both strings are variables, and one string
needs quotes around it for Excel to evaluate the formula properly.
The quotes are where I'm hanging up. Here is an example of my code:

AnVar1 = "" & AnVar & ""
Range("IV1").Formula = "=EXACT(" & AnVar1 & "," & ActiveCell.Address
&
")"

AnVar is something like Ag, or Al, etc. AnVar1 sets the string
correctly so it reads: "Ag" or "Al". When this is passed to the
formula the quotes are not included. So, in Excel in cell IV1 the
formula is: =Exact(Ag,"B1"). Since there no quotes around the Ag
text
the formula errors out.

I've tried various ways to get the quotes included like using double
quotes, plus signs, and & signs. These usually produce either errors
or passes straight variable text to the formula.

I would appreciate any help on this problem.

Thanks,
Amy

Variable in a Formula
 

Chip - Thanks so much! That did it. Now I can quit tearing my hair
out. :)

Thanks,
Amy


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!