Trouble with quotation marks in VBA

Posted by Ben O. on July 06, 2001 6:57 AM

I've got a macro that changes the data validation on a range of cells. I'm using List-type data validation, and my lists are on another worksheet. Because of this, I have to use quotation marks in my list formula, like this:

=INDIRECT(INDIRECT("'Client Lists'!A1")) as my validation formula.

But in the VBA procedure for changing the list formula, the formula has to go in quotation marks:

Range(x).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(INDIRECT(" 'Client Lists'!A1"))"

The problem is that Exel only reads what's between the first and second quotation mark as my formula. How can I make it read the whole thing?

-Ben

Posted by Ben O. on July 06, 2001 9:01 AM

Figured it out. Chr(34)

Posted by Ed C. on July 27, 2001 1:39 PM

Posted by Ed C. on July 27, 2001 1:43 PM

Ben I think I ran into the same thing, where when you want to do insert a formula into a cell using VBA it doesn't seem to compile. if yourt problem is like mine, you'll need to throw some double quotes around everything like this:

ActiveCell.Formula = _
"=IF(D2=""start"",IF(D3=""end"", """", ""EXCEPTION OCCURRED""), """")"

Let me know if this helps,
Ed