| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in macro causes macro to fail
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula in macro causes macro to fail
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula in macro causes macro to fail
Worked like a charm! Thanks !
"Bernie Deitrick" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula help - Sum results Pass or Fail | Excel Worksheet Functions | |||
| Formula & functions intermittently fail to calculate | Excel Worksheet Functions | |||
| Excel 2000 = upgraded to 2003 and macro to change font color fail | Excel Programming | |||
| A macro that copies and pastes autofiltered cells will fail. | Excel Programming | |||
| add-in macro strange errors--method sheets of object workbook fail | Excel Programming | |||
Hybrid Mode
