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'),'FTA', " & _
"IF(AND(RIGHT(H2,3)>='276',RIGHT(H2,3)<='299'),'Special
Retired',IF(AND(RIGHT(H2,3)>='300',RIGHT(H2,3)<='899'),'BROKER', " & _
"IF(AND(RIGHT(H2,3)>='900',RIGHT(H2,3)<='999'),'FR','UNASSIGNED'))))"

TIA

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'),'FTA', " & _
> "IF(AND(RIGHT(H2,3)>='276',RIGHT(H2,3)<='299'),'Special
> Retired',IF(AND(RIGHT(H2,3)>='300',RIGHT(H2,3)<='899'),'BROKER', " & _
> "IF(AND(RIGHT(H2,3)>='900',RIGHT(H2,3)<='999'),'FR','UNASSIGNED'))))"
>
> TIA

--

Dave Peterson

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'),'FTA', " & _
> > "IF(AND(RIGHT(H2,3)>='276',RIGHT(H2,3)<='299'),'Special
> > Retired',IF(AND(RIGHT(H2,3)>='300',RIGHT(H2,3)<='899'),'BROKER', " & _
> > "IF(AND(RIGHT(H2,3)>='900',RIGHT(H2,3)<='999'),'FR','UNASSIGNED'))))"
> >
> > TIA
>
> --
>
> Dave Peterson