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

shane_aldrich

Board Regular
Joined
Oct 23, 2009
Messages
148
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
I think you need to change lFstFormCol to i in the loop.
Code:
Set rForm1 = Range(Cells(4, i), Cells(4, i))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,211,748
Messages
6,103,686
Members
447,877
Latest member
OrangeJuice

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top