VBA code adds unwanted characters

B

BAC

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
 
O

OssieMac

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"
 
B

BAC

GREAT CATCH!

As many times as I looked at this I never spotted that=> but it fixed the
problem!

Thanx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top