VBA: Using For i, Next i to increment to the next column

[shane_aldrich]

I'm trying to piece part code together, and I thought this would be easy. In concept everything is working, but for some reason it won't go to the next i, or skip to the next column.

Not sure what to do. Any help would be awesome!!!!

Sub Test()
'*************************
'*** Declare Variables ***
'*************************
Dim ws1 As Worksheet

Dim i As Long
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

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

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

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

lFstFormCol = 26
lLstFormCol = 31
'***************
'*** Execute ***
'***************
'Formulas Filldown
For i = lFstFormCol To lLstFormCol

Set rForm1 = Range(Cells(4, lFstFormCol), Cells(4, lFstFormCol))
Set rForm2 = Range(Cells(4, lFstFormCol), Cells(lLastRow, lFstFormCol))
Set rForm3 = Range(Cells(5, lFstFormCol), Cells(lLastRow, lFstFormCol))

rForm1.Copy Destination:=rForm2
ws1.Calculate
rForm3 = rForm3.Value

Next i

End Sub

 

[shane_aldrich]

NEVERMIND...I'm an idiot

Sub Test()
'*************************
'*** Declare Variables ***
'*************************
Dim ws1 As Worksheet

Dim i As Long
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

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

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

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

lFstFormCol = 26
lLstFormCol = 31
'***************
'*** 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

 

[Norie]

I think you need to change lFstFormCol to i in the loop.

Set rForm1 = Range(Cells(4, i), Cells(4, i))

 

[craig.penny]

Do you need to replace lFstFormCol with i in this block?

Set rForm1 = Range(Cells(4, lFstFormCol), Cells(4, lFstFormCol))
Set rForm2 = Range(Cells(4, lFstFormCol), Cells(lLastRow, lFstFormCol))
Set rForm3 = Range(Cells(5, lFstFormCol), Cells(lLastRow, lFstFormCol))