Loading a formula from VB

[Michael]

I am attempting to load a formula from VB. I get an object defined error on
the final line of code below

Dim formula_str As String

Range(M4).Select

formula_str =
"=IF(ISERROR(VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE)),0,VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE))"
ActiveCell.FormulaR1C1 = formula_str

 

[Dave Peterson]

Without testing...

You wrote your formula in A1 reference style--not R1C1 reference style.

ActiveCell.Formula = formula_str

I am attempting to load a formula from VB. I get an object defined error on
the final line of code below

Dim formula_str As String

Range(M4).Select

formula_str =
"=IF(ISERROR(VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE)),0,VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE))"
ActiveCell.FormulaR1C1 = formula_str

 

[Michael]

I already tried the R1C1 style. That does not work either. It appears that
I will have to break the formula into two and use different columns.

Without testing...

You wrote your formula in A1 reference style--not R1C1 reference style.

ActiveCell.Formula = formula_str

I am attempting to load a formula from VB. I get an object defined error on
the final line of code below

Dim formula_str As String

Range(M4).Select

formula_str =
"=IF(ISERROR(VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE)),0,VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE))"
ActiveCell.FormulaR1C1 = formula_str

 

[Dave Peterson]

Maybe you should paste a copy of your code from the code window instead of
retyping it into the post.

This line failed for me:
Range(M4).Select
It should be
Range("M4").Select

But your formula worked ok for me if I had a workbook named filename.xls open
and changed the line to:

ActiveCell.Formula = formula_str

I already tried the R1C1 style. That does not work either. It appears that
I will have to break the formula into two and use different columns.

Without testing...

You wrote your formula in A1 reference style--not R1C1 reference style.

ActiveCell.Formula = formula_str

I am attempting to load a formula from VB. I get an object defined error on
the final line of code below

Dim formula_str As String

Range(M4).Select

formula_str =
"=IF(ISERROR(VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE)),0,VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE))"
ActiveCell.FormulaR1C1 = formula_str

 

[Michael]

Sorry - The M4 "M4" was a typo. I have a variable in my actual code but did
not want to confuse the issue here.

Removing the R1C1 from the ActiveCell.Formula line worked.

Thank You

Maybe you should paste a copy of your code from the code window instead of
retyping it into the post.

This line failed for me:
Range(M4).Select
It should be
Range("M4").Select

But your formula worked ok for me if I had a workbook named filename.xls open
and changed the line to:

ActiveCell.Formula = formula_str

I already tried the R1C1 style. That does not work either. It appears that
I will have to break the formula into two and use different columns.

Without testing...

You wrote your formula in A1 reference style--not R1C1 reference style.

ActiveCell.Formula = formula_str





Michael wrote:

I am attempting to load a formula from VB. I get an object defined error on
the final line of code below

Dim formula_str As String

Range(M4).Select

formula_str =
"=IF(ISERROR(VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE)),0,VLOOKUP(A4,'[filename.xls]sheet1'!$A$2:$B$100,2,FALSE))"
ActiveCell.FormulaR1C1 = formula_str