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

 

[MARK858]

You're welcome, happy we could help

 

[MARK858]

Sorry I should have said that you don't need to use Select / Selection (in fact you should avoid it).
Rather than

Columns("AO:AO").Select
    Selection.NumberFormat = "General"

just use

Columns("AO:AO").NumberFormat = "General"

 

[rachel06]

Sorry I should have said that you don't need to use Select / Selection (in fact you should avoid it).
Rather than

Columns("AO:AO").Select
    Selection.NumberFormat = "General"

just use

Columns("AO:AO").NumberFormat = "General"

Thank you!! I always just record and go from there. I so appreciate this kind of feedback.

 

[Joe4]

Thank you!! I always just record and go from there. I so appreciate this kind of feedback.

Yeah, recording is very literal, and often needs a little clean-up.
Most any time you have one row ending in "Select" and the next starting with "Selection" or "ActiveCelll", you can combine those two rows.
Not only does it make your code shorter, but faster to (as selecting things slows down your code).

It will also record things like screen scrolling, which can be deleted, as it is not critical to the functioning of the code.

 

[rachel06]

Yeah, recording is very literal, and often needs a little clean-up.
Most any time you have one row ending in "Select" and the next starting with "Selection" or "ActiveCelll", you can combine those two rows.
Not only does it make your code shorter, but faster to (as selecting things slows down your code).

It will also record things like screen scrolling, which can be deleted, as it is not critical to the functioning of the code.

Recording the scrolling is soo frustrating! I'm very appreciative of all the tips I've gotten here today and over the years. I'll definitely be spending some time cleaning up some coding in the next few weeks!

 

[Joe4]

You are welcome! Glad we could help!

Yeah, the Macro Recording is VERY literal and record most everything!

 

[MARK858]

@rachel06 Selecting will also cause your code to crash if the activesheet isn't the sheet referenced in the code.
For example if you had Sheet2 active and you ran

Sub XXX()
    Sheets("Sheet1").Range("A1:A2").Select
    Selection.Interior.ColorIndex = 6
End Sub

It would error, but the code below would color the cells on Sheet1

Sub YYY()
  Sheets("Sheet1").Range("A1:A2").Interior.ColorIndex = 6
End Sub

so there are various reasons to try and avoid Selecting

 

[rachel06]

@rachel06 Selecting will also cause your code to crash if the activesheet isn't the sheet referenced in the code.
For example if you had Sheet2 active and you ran

Sub XXX()
    Sheets("Sheet1").Range("A1:A2").Select
    Selection.Interior.ColorIndex = 6
End Sub

It would error, but the code below would color the cells on Sheet1

Sub YYY()
  Sheets("Sheet1").Range("A1:A2").Interior.ColorIndex = 6
End Sub

so there are various reasons to try and avoid Selecting

Good to note! I tend to just have a sheet that has all of my buttons so I (or anyone else) is always running from the same sheet.