"next i" loop instead of repeated code

[mgabriel2490]

i started out with code like this...

Sheets("DailyDisp").Range("a104").AutoFill Destination:=Sheets("DailyDisp").Range("A104").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
Sheets("DailyDisp").Range("b104").AutoFill Destination:=Sheets("DailyDisp").Range("b104").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
Sheets("DailyDisp").Range("c104").AutoFill Destination:=Sheets("DailyDisp").Range("c104").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
Sheets("DailyDisp").Range("g104").AutoFill Destination:=Sheets("DailyDisp").Range("g104").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
Sheets("DailyDisp").Range("h104").AutoFill Destination:=Sheets("DailyDisp").Range("h104").Resize(Sheets("Employees").Range("ap2").Value, 1)

but i want to look something like....

For i = a To m

Sheets("DailyDisp").Range("104" & i).AutoFill Destination:=Sheets("DailyDisp").Range("104" & i).Resize(Sheets("Employees").Range("ap2").Value, 1)
'Sheets("DailyDisp").Range("A104").AutoFill Destination:=Sheets("DailyDisp").Range("A104").Resize(Sheets("Employees").Range("ap2").Value, 1)
Next i

Additionally i have....

'Sheets("Master Data").Range("c105").AutoFill Destination:=Sheets("Master Data").Range("c105").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
'Sheets("Master Data").Range("d105").AutoFill Destination:=Sheets("Master Data").Range("d105").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
'Sheets("Master Data").Range("e105").AutoFill Destination:=Sheets("Master Data").Range("e105").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
'Sheets("Master Data").Range("f105").AutoFill Destination:=Sheets("Master Data").Range("f105").Resize(Sheets("Employees").Range("ap2").Value, 1)
 
'Sheets("Master Data").Range("h105").AutoFill Destination:=Sheets("Master Data").Range("h105").Resize(Sheets("Employees").Range("ap2").Value, 1)

that goes on and on through column YJ, so i need something like...

For i = c To YJSheets("Master Data").Range("105" & i).AutoFill Destination:=Sheets("Master Data").Range("105" & i).Resize(Sheets("Employees").Range("ap2").Value, 1)
'Sheets("Master Data").Range("c105").AutoFill Destination:=Sheets("Master Data").Range("c105").Resize(Sheets("Employees").Range("ap2").Value, 1)
Next i

what am i missing with these "i loops" ?

thank you all for your help in advance

 

[JoeMo]

oh it is a beautiful thing!!!

JoeMo... I cannot thank you enough!

Andrew Poulsom, thank you for the info... I will keep that in mind.

Everyone have a great weekend!!!

You are welcome.

Andrew has offered the simplest solution to your second question, but I thought you might want to see how to loop through column letters using numbers since that was the essence of your second question.

 

[mgabriel2490]

Welp....

I spoke to soon....

Dim colNum As Long, colLetter As String
For colNum = 3 To 660
colLetter = Split(Columns(660).Address(0, 0), ":")(0)


Sheets("Master Data").Range(colLetter & 105).AutoFill Destination:=Sheets("Master Data").Range(colLetter & 105).Resize(Sheets("Employees").Range("ap2").Value, 1)
Next colNum

Ran the macro and it stopped at column HA. And I don't have a clue why. There is no change in consistency for data, formulas, etc.

 

[mgabriel2490]

I used Andrew's method...

For i = 3 To 660
Sheets("Master Data").Cells(105, i).AutoFill Destination:=Sheets("Master Data").Cells(105, i).Resize(Sheets("Employees").Range("ap2").Value, 1)
Next i

And I think it worked fully. Not sure why it didn't the other way, but would like to know if anyone has an idea.


Again....thank you both!

 

[JoeMo]

Welp....

I spoke to soon....

Dim colNum As Long, colLetter As String
For colNum = 3 To 660
[COLOR=#ff0000][B]colLetter = Split(Columns(660)[/B][/COLOR].Address(0, 0), ":")(0)


Sheets("Master Data").Range(colLetter & 105).AutoFill Destination:=Sheets("Master Data").Range(colLetter & 105).Resize(Sheets("Employees").Range("ap2").Value, 1)
Next colNum

Ran the macro and it stopped at column HA. And I don't have a clue why. There is no change in consistency for data, formulas, etc.

That's not the code I posted in post #6, the red font is not right - replace 660 with colNum.

 

[mgabriel2490]

Ahhhh, I see!

My mistake once again. Thank you for that.

 

[JoeMo]

Ahhhh, I see!

My mistake once again. Thank you for that.

You are welcome - thanks for the reply.