|
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! |
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! |
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 |
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 |
| All times are GMT +1. The time now is 12:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com