Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Defined Error setting a formula via VBA
The following code is giving me a Application Defined or Object Defined
error trying to set a formula for cell I for each row: Dim wb As Workbook Dim ws As Worksheet Dim res As Variant Set wb = ActiveWorkbook Set ws = wb.Worksheets("Data") Set rng = Cells(Rows.Count, 1).End(xlUp) For i = rng.Row To 2 Step -1 alias2 = Sheets("Data").Rows(i).Cells(3).Value ws.Cells(i, "C").Value = Application.VLookup(alias2, _ Sheets("PolicyStatus").Range("A:B"), 2, False) 'The above works 'Below getting error ws.Cells(i, "I").Formula = "=IF(AND(RIGHT(H2,3)='001',RIGHT(H2,3)<='275'),'F TA', " & _ "IF(AND(RIGHT(H2,3)='276',RIGHT(H2,3)<='299'),'Sp ecial Retired',IF(AND(RIGHT(H2,3)='300',RIGHT(H2,3)<='8 99'),'BROKER', " & _ "IF(AND(RIGHT(H2,3)='900',RIGHT(H2,3)<='999'),'FR ','UNASSIGNED'))))" TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Defined Error setting a formula via VBA
In excel formulas, strings are surrounded by double quotes.
If you were typing the formula in a cell, you'd see: =IF(AND(RIGHT(H2,3)="001", .... But when you really want a double quote in VBA, you have to use two of them: ws.Cells(i, "I").Formula = _ "=IF(AND(RIGHT(H2,3)=""001"", ... Then try it out. When I'm debugging my long formulas, I like to do this: ws.Cells(i, "I").Formula = _ "IF(AND(RIGHT(H2,3)=""001"", ... Then I can go back to that cell and add the leading equal sign and see how I screwed it up. Then back to the VBE and make another attempt. BerkshireGuy wrote: The following code is giving me a Application Defined or Object Defined error trying to set a formula for cell I for each row: Dim wb As Workbook Dim ws As Worksheet Dim res As Variant Set wb = ActiveWorkbook Set ws = wb.Worksheets("Data") Set rng = Cells(Rows.Count, 1).End(xlUp) For i = rng.Row To 2 Step -1 alias2 = Sheets("Data").Rows(i).Cells(3).Value ws.Cells(i, "C").Value = Application.VLookup(alias2, _ Sheets("PolicyStatus").Range("A:B"), 2, False) 'The above works 'Below getting error ws.Cells(i, "I").Formula = "=IF(AND(RIGHT(H2,3)='001',RIGHT(H2,3)<='275'),'F TA', " & _ "IF(AND(RIGHT(H2,3)='276',RIGHT(H2,3)<='299'),'Sp ecial Retired',IF(AND(RIGHT(H2,3)='300',RIGHT(H2,3)<='8 99'),'BROKER', " & _ "IF(AND(RIGHT(H2,3)='900',RIGHT(H2,3)<='999'),'FR ','UNASSIGNED'))))" TIA -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Defined Error setting a formula via VBA
Dave:
Perfect! Thanks for quick response! -B Dave Peterson wrote: In excel formulas, strings are surrounded by double quotes. If you were typing the formula in a cell, you'd see: =IF(AND(RIGHT(H2,3)="001", .... But when you really want a double quote in VBA, you have to use two of them: ws.Cells(i, "I").Formula = _ "=IF(AND(RIGHT(H2,3)=""001"", ... Then try it out. When I'm debugging my long formulas, I like to do this: ws.Cells(i, "I").Formula = _ "IF(AND(RIGHT(H2,3)=""001"", ... Then I can go back to that cell and add the leading equal sign and see how I screwed it up. Then back to the VBE and make another attempt. BerkshireGuy wrote: The following code is giving me a Application Defined or Object Defined error trying to set a formula for cell I for each row: Dim wb As Workbook Dim ws As Worksheet Dim res As Variant Set wb = ActiveWorkbook Set ws = wb.Worksheets("Data") Set rng = Cells(Rows.Count, 1).End(xlUp) For i = rng.Row To 2 Step -1 alias2 = Sheets("Data").Rows(i).Cells(3).Value ws.Cells(i, "C").Value = Application.VLookup(alias2, _ Sheets("PolicyStatus").Range("A:B"), 2, False) 'The above works 'Below getting error ws.Cells(i, "I").Formula = "=IF(AND(RIGHT(H2,3)='001',RIGHT(H2,3)<='275'),'F TA', " & _ "IF(AND(RIGHT(H2,3)='276',RIGHT(H2,3)<='299'),'Sp ecial Retired',IF(AND(RIGHT(H2,3)='300',RIGHT(H2,3)<='8 99'),'BROKER', " & _ "IF(AND(RIGHT(H2,3)='900',RIGHT(H2,3)<='999'),'FR ','UNASSIGNED'))))" TIA -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error 1004- application defined or object defined error | Excel Programming | |||
Runtime error 1004- application defined or object defined error | Excel Programming | |||
error: ActiveCell.Offset(0, -1).Select = Application-defined or object-defined error | Excel Programming | |||
How to set a formula in a cell? Application-defined error. | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming |