AutoFill or Copy Formula to All Relevant Cells

[deZine]

Hi Everyone,
I would like to autofill or copy this formula

=IF($C2=E$1,$D2,"")

(this is the formula when it is in cell E2) to all columns that have text in Row 1 beginning with column E. It should autofill down to the last row that is not blank in column A.
I think this code sort of does the job but the autofill doesn't automatically figure out how many columns and rows to copy the formula to:

    Range("E2").Value = "=IF(RC3=R1C,RC4,"""")"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E35912")
    
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:O2"), Type:=xlFillDefault
    
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F35912")
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G35912")
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H35912")
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I35912")
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J35912")
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K35912")
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L35912")
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M35912")
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N35912")
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O35912")

Thanks so much for any help you can offer!
P.S. If you could offer any advice on how to remove all the selecting when I record a macro that would be great also!

 

[Joe4]

Try this:

    Dim lr As Long
    Dim lc As Long
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Find last column in Row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Populate all rows and cells starting with cell E2
    Range(Cells(2, 5), Cells(lr, lc)).FormulaR1C1 = "=IF(RC3=R1C,RC4,"""")"

 

[Joe4]

P.S. If you could offer any advice on how to remove all the selecting when I record a macro that would be great also!

To answer this other question, note that the Macro Recorder is very literal and records each selection, scroll, etc.
A lot of this can be removed or combined.

Most of the time when you have one line that ends with "Select" and the next line begins with "Selection" or "ActiveCell", these two lines can be combined.
So sections like this:

    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O35912")

could be combined to this:

    Range("O2").AutoFill Destination:=Range("O2:O35912")

 

[deZine]

Thank you Joe! This works perfectly! And thank you for the pro tip on removing the selecting.

 

[Joe4]

You are welcome!
Glad I was able to help.

Also note that the method I used in my code is a little different. Rather than use Autofill, I simply applied the formula to the whole range of cells at once.
The "R1C1" formula format means the formula is "relative to the cell you put it in".

 

[deZine]

I noticed that. It does exactly what I needed. Thanks again!