Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code adds unwanted characters
XP Pro; Office 2007 SP1
I have a VBA routine that refreshes the formulas in a worksheet. Several of the formulas involve a Vlookup off a specific cell. Sub sb_ResetWireForm() nd_row = Sheets("DATAAREA").Cells(Rows.Count, "A").End(xlUp).Row Range("E19:G19").ClearContents Range("E21:G21").ClearContents Range("E24").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,3,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,3,FALSE))" Range("E27:G27").ClearContents Range("E28:G28").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,2,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,2,FALSE))" Range("E29:G29").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,5,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,5,FALSE))" Range("E30:G30").Select ActiveCell.FormulaR1C1 = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,6,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,6,FALSE))" Range("E31:G31").ClearContents Range("E32:G32").ClearContents Range("E33:G33").ClearContents Range("E34:G34").ClearContents Range("H34").Select End Sub Note that all the "IF(ISNA(" formulas reference cell E27. They all work fine except for the last which insists on entering =IF(ISNA(VLOOKUP('E27',DATAAREA!lg_Table,6,FALSE)) ,"""",VLOOKUP('E27',DATAAREA!lg_Table,6,FALSE)) <= Note the single quotes around the E7 cell reference both times! I have tried deleted row 30 and rerun this, changed the row 30 to row 31, and changed the lookup column t 5 instaed of 6 (matching the earlier formula) but nothing seems to change VBA's insistence on adding the ' on the 4th entry of the formula.. Any help would be appreciated.. TIA BAC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code adds unwanted characters
Without actually testing I would say it has something to do with .FormulaR1C1
Your other code uses .FormulaLocal Using R1C1 style indicates the formula should be using Row and column numbers referencing in the code in lieu of A1 style refererencing. In fact when coding formulas using A1 style referencing there is no need to specify that last part. Just ActiveCell = "the formula here" -- Regards, OssieMac "BAC" wrote: XP Pro; Office 2007 SP1 I have a VBA routine that refreshes the formulas in a worksheet. Several of the formulas involve a Vlookup off a specific cell. Sub sb_ResetWireForm() nd_row = Sheets("DATAAREA").Cells(Rows.Count, "A").End(xlUp).Row Range("E19:G19").ClearContents Range("E21:G21").ClearContents Range("E24").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,3,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,3,FALSE))" Range("E27:G27").ClearContents Range("E28:G28").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,2,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,2,FALSE))" Range("E29:G29").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,5,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,5,FALSE))" Range("E30:G30").Select ActiveCell.FormulaR1C1 = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,6,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,6,FALSE))" Range("E31:G31").ClearContents Range("E32:G32").ClearContents Range("E33:G33").ClearContents Range("E34:G34").ClearContents Range("H34").Select End Sub Note that all the "IF(ISNA(" formulas reference cell E27. They all work fine except for the last which insists on entering =IF(ISNA(VLOOKUP('E27',DATAAREA!lg_Table,6,FALSE)) ,"""",VLOOKUP('E27',DATAAREA!lg_Table,6,FALSE)) <= Note the single quotes around the E7 cell reference both times! I have tried deleted row 30 and rerun this, changed the row 30 to row 31, and changed the lookup column t 5 instaed of 6 (matching the earlier formula) but nothing seems to change VBA's insistence on adding the ' on the 4th entry of the formula.. Any help would be appreciated.. TIA BAC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code adds unwanted characters
GREAT CATCH!
As many times as I looked at this I never spotted that= but it fixed the problem! Thanx "OssieMac" wrote: Without actually testing I would say it has something to do with .FormulaR1C1 Your other code uses .FormulaLocal Using R1C1 style indicates the formula should be using Row and column numbers referencing in the code in lieu of A1 style refererencing. In fact when coding formulas using A1 style referencing there is no need to specify that last part. Just ActiveCell = "the formula here" -- Regards, OssieMac "BAC" wrote: XP Pro; Office 2007 SP1 I have a VBA routine that refreshes the formulas in a worksheet. Several of the formulas involve a Vlookup off a specific cell. Sub sb_ResetWireForm() nd_row = Sheets("DATAAREA").Cells(Rows.Count, "A").End(xlUp).Row Range("E19:G19").ClearContents Range("E21:G21").ClearContents Range("E24").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,3,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,3,FALSE))" Range("E27:G27").ClearContents Range("E28:G28").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,2,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,2,FALSE))" Range("E29:G29").Select ActiveCell.FormulaLocal = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,5,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,5,FALSE))" Range("E30:G30").Select ActiveCell.FormulaR1C1 = _ "=IF(ISNA(VLOOKUP(E27,DATAAREA!lg_Table,6,FALSE)), """",VLOOKUP(E27,DATAAREA!lg_Table,6,FALSE))" Range("E31:G31").ClearContents Range("E32:G32").ClearContents Range("E33:G33").ClearContents Range("E34:G34").ClearContents Range("H34").Select End Sub Note that all the "IF(ISNA(" formulas reference cell E27. They all work fine except for the last which insists on entering =IF(ISNA(VLOOKUP('E27',DATAAREA!lg_Table,6,FALSE)) ,"""",VLOOKUP('E27',DATAAREA!lg_Table,6,FALSE)) <= Note the single quotes around the E7 cell reference both times! I have tried deleted row 30 and rerun this, changed the row 30 to row 31, and changed the lookup column t 5 instaed of 6 (matching the earlier formula) but nothing seems to change VBA's insistence on adding the ' on the 4th entry of the formula.. Any help would be appreciated.. TIA BAC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unwanted characters in a TextBox | Excel Programming | |||
Unwanted characters saving to fie | Excel Programming | |||
Removing unwanted characters | Excel Discussion (Misc queries) | |||
Removing unwanted characters | Excel Discussion (Misc queries) | |||
Removing unwanted characters | Excel Discussion (Misc queries) |