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

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

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