# fill down, adjusting for columns • ### Question

• Morning all.

I have a project I'm working on where I need to fill down an equation, and change column # after 40 rows (rows 4 through 43), and then at the end of the 1480 iterations, change the sheet name.

My base equation is:

='16'!B\$4

where 16 is the first sheet, and it'll go through sheet # 31

the columns are from B through AM.

This will be a single column's output.

Ok, it appears that I'm over-stating this.

I can make the code for the equations. I've done that in the past, and my code still works to this day.

I guess the issue I'm having is getting the loop down which would allow me to iterate through each new group on the single column.

Because this is a 40 row grouping, my equation will be changing each 40 rows.

It'll start

='16'!B4 through ='16'!B43

='16'!C4 through ='16'!C43

..................

='16'!AM4 through ='16'!AM43

Thus, I'm seeing a two loop operation.

for each i = 1 to 40 ()

for each j = b to am (Column #-ing, would I be able to do numbers on the columns?)

If so, it'd be j = 1 to 38

i.row.count

j.column.count

TYIA....

• Edited by Tuesday, September 27, 2016 2:34 AM better explanation?
Monday, September 26, 2016 2:37 PM

• If I am interpreting your question correctly you want to know how to convert the alpha column Id to a numeric. Is this correct? If so, then the following example.

Dim lngColFirst As Long
Dim lngColLast As Long
Dim J As Long

lngColFirst = Columns("B").Column
lngColLast = Columns("AM").Column

For J = lngColFirst To lngColLast
Next J

'Alternatively can use the following without first assigning the column numbers to variables
For J = Columns("B").Column To Columns("AM").Column
Next J

Regards, OssieMac

• Proposed as answer by Monday, October 3, 2016 4:27 AM
• Marked as answer by Wednesday, January 11, 2017 3:28 PM
Wednesday, September 28, 2016 6:10 AM
• Hi,

Do you want to write every cell formula by using FOR Loop?

I suggest you try the following autofill method:

```Range("B1").Formula = "='1'!B4"
Range("B1").Select
''vertical filling:autofill B1:B40
''horizontal filling: autofill B1:AM1

'autofill B1:AM40
Selection.AutoFill Destination:=Range("B1" & ":" & ActiveCell.Offset(39, 37).Address), Type:=xlFillDefault```

• Proposed as answer by Monday, October 3, 2016 4:27 AM
• Marked as answer by Wednesday, October 5, 2016 3:36 AM
Wednesday, September 28, 2016 11:29 AM

### All replies

• If I am interpreting your question correctly you want to know how to convert the alpha column Id to a numeric. Is this correct? If so, then the following example.

Dim lngColFirst As Long
Dim lngColLast As Long
Dim J As Long

lngColFirst = Columns("B").Column
lngColLast = Columns("AM").Column

For J = lngColFirst To lngColLast
Next J

'Alternatively can use the following without first assigning the column numbers to variables
For J = Columns("B").Column To Columns("AM").Column
Next J

Regards, OssieMac

• Proposed as answer by Monday, October 3, 2016 4:27 AM
• Marked as answer by Wednesday, January 11, 2017 3:28 PM
Wednesday, September 28, 2016 6:10 AM
• Note in my previous post that J will be 2 to 39 and not 1 to 38.  Because 2 to 39 is actual column numbers, you should be able to use those numbers directly in your code. (For loop does not have to start at one).

Regards, OssieMac

Wednesday, September 28, 2016 6:14 AM
• Hi,

Do you want to write every cell formula by using FOR Loop?

I suggest you try the following autofill method:

```Range("B1").Formula = "='1'!B4"
Range("B1").Select
''vertical filling:autofill B1:B40