Variable in a Formula

[Amy Ja]

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

 

[Chip Pearson]

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

 

[Frank Kabel]

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

 

[Amy]

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!