Formula in macro causes macro to fail

[KCK]

Hi, I am pretty new to writing macro's and need some help. I am writing a
macro to insert some formulas into cells. It inserts my first formula fine
but when it gets to the second formula it fails and ask if I want to debug.
The second formula is doing a vlookup on a second file using an IF(ISERROR)
functions to determine which file it finds the matched data in. the formula
works great outside the macro. Any help would be greatly appreciated.
Here is a small portion of the macro.

Thanks,
Kerry

Sub Macro3()
'
' Macro3 Macro
'
Range("Q1").Select
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "."
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlUp).Select

' This formula works fine ------------------------------------
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _

"=TRIM(CLEAN(SUBSTITUTE(LEFT(TRIM(RC[-14]),LEN(TRIM(RC[-14]))-OR(RIGHT(TRIM(RC[-14]))={""?"",""!"","".""})),CHAR(160),"" "")))"

' The macro stops on this formula---------------------------
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-2],'[KK-Chargeback Sales Order Freight
Details.xls]Sales Pays'!$S:$T,2,FALSE)),VLOOKUP(RC[-2],'[KK-Chargeback
Inbound Cost at PO Receipt.xls]By
Receiver'!$S:$T,2,FALSE),VLOOKUP(RC[-2],'[KK-Chargeback Sales Order Freight
Details.xls]Sales Pays'!$S:$T,2,FALSE))"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "s"
End Sub

 

[Bernie Deitrick]

KCK,

You are mixing your cell reference modes: $S$T is not R1C1. Record a macro, select the cell with
the formula, press F2, then press enter, and you will see the difference in the code.

HTH,
Bernie
MS Excel MVP

 

[KCK]

Worked like a charm! Thanks !

KCK,

You are mixing your cell reference modes: $S$T is not R1C1. Record a macro, select the cell with
the formula, press F2, then press enter, and you will see the difference in the code.

HTH,
Bernie
MS Excel MVP

Hi, I am pretty new to writing macro's and need some help. I am writing a
macro to insert some formulas into cells. It inserts my first formula fine
but when it gets to the second formula it fails and ask if I want to debug.
The second formula is doing a vlookup on a second file using an IF(ISERROR)
functions to determine which file it finds the matched data in. the formula
works great outside the macro. Any help would be greatly appreciated.
Here is a small portion of the macro.

Thanks,
Kerry

Sub Macro3()
'
' Macro3 Macro
'
Range("Q1").Select
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "."
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlUp).Select

' This formula works fine ------------------------------------
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _

"=TRIM(CLEAN(SUBSTITUTE(LEFT(TRIM(RC[-14]),LEN(TRIM(RC[-14]))-OR(RIGHT(TRIM(RC[-14]))={""?"",""!"","".""})),CHAR(160),""
"")))"

' The macro stops on this formula---------------------------
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-2],'[KK-Chargeback Sales Order Freight
Details.xls]Sales Pays'!$S:$T,2,FALSE)),VLOOKUP(RC[-2],'[KK-Chargeback
Inbound Cost at PO Receipt.xls]By
Receiver'!$S:$T,2,FALSE),VLOOKUP(RC[-2],'[KK-Chargeback Sales Order Freight
Details.xls]Sales Pays'!$S:$T,2,FALSE))"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "s"
End Sub