Quick Inverted Comma Question

[scott_smith]

Hi,

Can anyone help me work out the correct syntax for this in VBA? I'm
pretty sure the problem is to do with nesting of of inverted commas...
It's driving me crazy!

Activecell.Value = "=IF(ISNA(VLOOKUP(TRIM($A3),INDIRECT(""&" &
Left(EndAddress,1) &"""$2&"""!$P$2:$X$""" &DailyFinalRow &"),
9,FALSE))", 0, VLOOKUP(TRIM($A3),INDIRECT(""""&" & Left(EndAddress,1)
&"$2&"!$P$2:$X$" &DailyFinalRow &"),9,FALSE))"


The final results should be this:

=IF(ISNA(VLOOKUP(TRIM($A3),INDIRECT(""&D$2&"!$P$2:$X$32"),9,FALSE)),
0, VLOOKUP(TRIM($A3),INDIRECT(""&D$2&"!$P$2:$X$32"),9,FALSE))

Thanks!

 

[Bernie Deitrick]

Scott,

Get the formula to work in a cell, select that cell, turn on the macro recorder, press F2, then
press Enter. Then make changes to the code that results, or post it here. But I guarantee that

Left(EndAddress,1) &"""$2&"""!$P$2:$X$

will not result in a valid sheetname / range address combination....


HTH,
Bernie
MS Excel MVP

 

[scott_smith]

Scott,

Get the formula to work in a cell, select that cell, turn on the macro recorder, press F2, then
press Enter. Then make changes to the code that results, or post it here. But I guarantee that

Left(EndAddress,1) &"""$2&"""!$P$2:$X$

will not result in a valid sheetname / range address combination....

HTH,
Bernie
MS Excel MVP











- Show quoted text -

Thanks for the reply Bernie.

I've just tried what you suggested & it gives me this code:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(TRIM(RC1),INDIRECT(""""&R2C&""!$P$2:$X$32""),
9,FALSE)), 0, VLOOKUP(TRIM(RC1),INDIRECT(""""&R2C&""!$P$2:$X$32""),
9,FALSE))"

I've tried editing this to incorporate the parameters I need, but it
still doesn't seem to work!

Can you help with this anymore?

Thanks,
Scott

 

[Bernie Deitrick]

Scott,

Try these:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(TRIM(RC1),INDIRECT(R2C & ""!$P$2:$X$32""),9,FALSE))," _
& "0, VLOOKUP(TRIM(RC1),INDIRECT( R2C & ""!$P$2:$X$32""), 9,FALSE))"


or if "EndAddress" refers to a named cell:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(TRIM(RC1),INDIRECT(LEFT(EndAddress,1)" _
& " & ""!$P$2:$X$32""),9,FALSE))," _
& " 0, VLOOKUP(TRIM(RC1),INDIRECT(LEFT(EndAddress,1)" _
& " & ""!$P$2:$X$32""), 9,FALSE))"

or if "EndAddress" refers to a variable:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(TRIM(RC1),INDIRECT(""" & Left(EndAddress, 1) & """" _
& " & ""!$P$2:$X$32""),9,FALSE))," _
& " 0, VLOOKUP(TRIM(RC1),INDIRECT(""" & Left(EndAddress, 1) & """" _
& " & ""!$P$2:$X$32""), 9,FALSE))"

HTH,
Bernie
MS Excel MVP