VBA Copy formula down data set

[rachel06]

Hi,

Range("AD2:AD" & LastRow).Formula = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""R"",""Retail"",IF(RC[-4]=""M"",""Mail"",""""))))"

Trying to get this formula to copy down column AD for the entire dataset and getting an error "Method "Range' of object' _Global' failed.

Just curious what I'm doing wrong here

 

[Joe4]

How are you calculating "LastRow"?
What is its value when you get to this line of code?

 

[rachel06]

How are you calculating "LastRow"?
What is its value when you get to this line of code?

Oh shoot. Is this one of those things where I need to declare what "LastRow" is? I'm in this forum so much you'd think I'd know these things by now but I'm kind of a scatterbrain sometimes.

This is the entire code right now:

   Sheets("1537").Range("AO:AO").EntireColumn.Insert Shift:=xlToRight
    Sheets("1537").Range("AO1").Value = "Claim Type"
    Sheets("1537").Range("AO2:AO" & LastRow).Formula = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""R"",""Retail"",IF(RC[-4]=""M"",""Mail"",""Mail""))))"

I added the sheet name since last time thinking that might do it but no avail

 

[Joe4]

Not just declare, but you also need to sets its value!
Since it is a user defined variable, its value is nothing (or zero if declared to be a number) until you set it equal to something.

So you need some sort of calculation in there to determine the last row.

You can temporarily add this MsgBox to confirm you have a good value before using it.

    MsgBox LastRow
    Sheets("1537").Range("AO2:AO" & LastRow).Formula = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""R"",""Retail"",IF(RC[-4]=""M"",""Mail"",""Mail""))))"

 

[rachel06]

Not just declare, but you also need to sets its value!
Since it is a user defined variable, its value is nothing (or zero if declared to be a number) until you set it equal to something.

So you need some sort of calculation in there to determine the last row.

You can temporarily add this MsgBox to confirm you have a good value before using it.

    MsgBox LastRow
    Sheets("1537").Range("AO2:AO" & LastRow).Formula = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""R"",""Retail"",IF(RC[-4]=""M"",""Mail"",""Mail""))))"

Got it. This will at least get me down the right path. Thanks as always!

 

[Joe4]

You are welcome.

Yes, somewhere up higher in your code, you need to have your LastRow calculation.
It should start something like this:

LastRow = ...

 

[rachel06]

You are welcome.

Yes, somewhere up higher in your code, you need to have your LastRow calculation.
It should start something like this:

LastRow = ...

Okay, I got that with LastRow = Cells(Rows.Count, "AO").End(xlUp).Row, but now it's pasting the text of the formula and not the result of the formula lol.

Sub Macro4()
    LastRow = Cells(Rows.Count, "AO").End(xlUp).Row
    Sheets("1537").Range("AO:AO").EntireColumn.Insert Shift:=xlToRight
    Sheets("1537").Range("AO1").Value = "Claim Type"
    Sheets("1537").Range("AO2:AO" & LastRow).Formula = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""R"",""Retail"",IF(RC[-4]=""M"",""Mail"",""Mail""))))"
    
    End Sub

Do I have too many quotes?

 

[MARK858]

Try changing

Sheets("1537").Range("AO2:AO" & LastRow).Formula

to

Sheets("1537").Range("AO2:AO" & LastRow).FormulaR1C1

Also make sure your cells are formatted as General and not Text before you run the code

 

[rachel06]

Try changing

Sheets("1537").Range("AO2:AO" & LastRow).Formula

to

Sheets("1537").Range("AO2:AO" & LastRow).FormulaR1C1

Also make sure your cells are formatted as General and not Text before you run the code

How do I force it to general?

This didn't do it - it was flipped back to text when the macro finished

Sub Macro4()
    LastRow = Cells(Rows.Count, "AO").End(xlUp).Row
     Columns("AO:AO").Select
    Selection.NumberFormat = "General"
    Sheets("1537").Range("AO:AO").EntireColumn.Insert Shift:=xlToRight
    Sheets("1537").Range("AO1").Value = "Claim Type"
    Sheets("1537").Range("AO2:AO" & LastRow).Formula = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""R"",""Retail"",IF(RC[-4]=""M"",""Mail"",""Mail""))))"
    
    

End Sub

 

[rachel06]

How do I force it to general?

This didn't do it - it was flipped back to text when the macro finished

Sub Macro4()
    LastRow = Cells(Rows.Count, "AO").End(xlUp).Row
     Columns("AO:AO").Select
    Selection.NumberFormat = "General"
    Sheets("1537").Range("AO:AO").EntireColumn.Insert Shift:=xlToRight
    Sheets("1537").Range("AO1").Value = "Claim Type"
    Sheets("1537").Range("AO2:AO" & LastRow).Formula = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""R"",""Retail"",IF(RC[-4]=""M"",""Mail"",""Mail""))))"
   
   

End Sub

oh wait, i put it in the wrong order. ha. all good now. Thanks so much all!