Excel VBA Column Loop

[Sforster]

Hi all,

Here is my problem:

I am trying to loop through a number of columns and in each column autofill down. Here is the code I am trying to use

for i = 1 to Nocards

TopCell = cells(rowcount, i + 1).address
Bottomcell = cells(rowcount + 1, i + 1).address
Insert_which = Topcell & ":" & Bottomcell
Range(Insert_which).select
selection.Autofill Destination:=Range(Insert_Which), Type:=Xlfilldefault

next i

This doesn't work !! I have tried to address the columns and everything...Now starting to bang head against a wall !!

Anybody got any ideas on a better way or some way to modify the above code. Using the columns property returns a number and not the corresponding letter i.e.

TopCell = cells(rowcount, i + 1).column

returns a 2, and not B

Please help !! Any ideas greatly welcomed

Steve

[HughLacey]

It's not entirely clear what you are trying to do, but the following line of code will autofill down the first three columns of sheet1 for ten rows:

dim s as worksheet

Set s = Application.Sheets("Sheet1")
s.Range("A1:C2").AutoFill Destination:=s.Range("A1:c10")

Hopefully this will help!

[Sforster]

I have just realised that you can autofill over more than one column (so I don't have to loop).

Problem solved but thanks for the response

Cheers
Steve

[Gaffer]

OK, here’s a solution, but is not based on AutoFill, but FillDown. I have also removed A1 type references to the cells and used row column references (I find them easier to use in code).

 Dim nocards, rowcount
Dim topX, topY, botX, botY

For i = 1 To nocards
    topX = rowcount
    topY = i + 1
    botX = rowcount + 1
    botY = topY
        
    Range(Cells(topX, topY), Cells(botX, botY)).Select
    
    Selection.FillDown

Next I

I hope this saves your bruised head…

Gaffer