VBA Formula Filldown 10 Columns at a time until last column

[shane_aldrich]

I use the code below filldown formulas, it works great, but takes forever. There are too many columns/rows to filldown the entire range without me getting a memory error, so instead, I'd like the second "i" in each of the formula ranges to be i+10

i+10, so it would fill down 10 at a time, but I'm not dealing with increments fo 10, so I'm not sure how to handle when i+10 exceeds lLstFormCol...so if i+10 exceeds lLstFormCol, do i+lLstFormCol

Sub DTR_Raw_04_Formulas()

'*************************
'*** Declare Variables ***
'*************************
Dim ws1 As Worksheet
Dim lLastRow As Long
Dim lLastCol As Long

Dim lFstFormCol As Long
Dim lLstFormCol As Long
Dim rForm1 As Range
Dim rForm2 As Range
Dim rForm3 As Range

Dim rng1 As Range

'*********************
'*** Set Variables ***
'*********************

Set ws1 = Sheets("Raw_DTR")
ws1.Activate

lLastRow = Last(1, Columns(1))
lLastCol = Last(2, Rows(1))

lFstFormCol = 11
lLstFormCol = 99

'***************
'*** Execute ***
'***************

'Formulas Filldown
For i = lFstFormCol To lLstFormCol

Set rForm1 = Range(Cells(4, i), Cells(4, i))
Set rForm2 = Range(Cells(4, i), Cells(lLastRow, i))
Set rForm3 = Range(Cells(5, i), Cells(lLastRow, i))
rForm1.Copy Destination:=rForm2
ws1.Calculate
rForm3 = rForm3.Value
Next i

End Sub

 

[Jerry Sullivan]

Approximately how many rows and columns of data do you have in Sheets("Raw_DTR")?

What is the formula that you are copying from Cell K4?

You should be able to apply the formula using Range.FormulaR1C1 without the need for the intervening ws1.Calculate.

 

[shane_aldrich]

i figured out the loop part, but is that because the value function recalculates before it converts them to values, the reason I ask is because I was watching the code and it did look like it was calculating twice?

 

[Jerry Sullivan]

If you can answer the questions in my first reply, I'll try to help.