| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Inverted Comma Question
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Quick Inverted Comma Question
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 wrote in message oups.com... 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Quick Inverted Comma Question
On 4 May, 16:02, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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 wrote in message oups.com... 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!- Hide quoted text - - 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Quick Inverted Comma Question
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(EndAddre ss,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 wrote in message oups.com... On 4 May, 16:02, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 wrote in message oups.com... 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!- Hide quoted text - - 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comma Style Question | New Users to Excel | |||
| How to add an inverted comma in a cell? | Excel Worksheet Functions | |||
| Quick question - quick answer about assigning shortcut keys | Excel Programming | |||
| Comma delimited question | Excel Discussion (Misc queries) | |||
| Quick Question??? | Excel Programming | |||
Hybrid Mode
