VBA not posting down the column

[ianawwalker]

Hello,

I have this formula that I want to have filled down to the last row in column A, which could go down 20-30K rows. The VBA I am currently using is only pasting within row 2 of each of the columns and i can't figure out why. Any help would be greatly appreciated.

With Sheets("US_Combine")

.Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row).Formula2R1C1 = _
"=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
.Range("D2:D" & Cells(Rows.Count, 3).End(xlUp).Row).Formula2R1C1 = _
"=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
.Range("E2:E" & Cells(Rows.Count, 3).End(xlUp).Row).Formula2R1C1 = _
"=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
.Range("F2:F" & Cells(Rows.Count, 3).End(xlUp).Row).Formula2R1C1 = _
"=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"

End With

Thank you,

Ian

 

[MARK858]

If it is to the last cell in column A the red 3 should be a 1.

.Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)

there should also be a dot in front of Cells

.Range("C2:C" & .Cells(Rows.Count, 1).End(xlUp).Row)

 

[ianawwalker]

that worked great, but took around 3 min to go into all 4 of the columns. do you know a way to speed it up at all?

thank you!!

 

[MARK858]

Only turning your screenupdating off and setting your calculation to manual while entering the formula (remember to set the calculation back to automatic after the formulas have been entered)

 

[ianawwalker]

sounds good, thank you for the help Mark!

 

[MARK858]

You're welcome

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With Sheets("US_Combine")

        .Range("C2:C" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
                    "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
        .Range("D2:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
                    "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
        .Range("E2:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
                    "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
        .Range("F2:F" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
                    "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"

    End With


    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With