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 |
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 |
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 |
All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com