Formula in macro causes macro to fail

K

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
 
B

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
 
K

KCK

Worked like a charm! Thanks !

Bernie Deitrick said:
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 said:
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
 

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