locked
fill down, adjusting for columns RRS feed

  • 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.

    Addendum----
    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 SteveDB1 Tuesday, September 27, 2016 2:34 AM better explanation?
    Monday, September 26, 2016 2:37 PM

Answers

  • 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
            'Your code in here
        Next J
       
        'Alternatively can use the following without first assigning the column numbers to variables
        For J = Columns("B").Column To Columns("AM").Column
            'Your code in here
        Next J


    Regards, OssieMac

    • Proposed as answer by Chenchen Li Monday, October 3, 2016 4:27 AM
    • Marked as answer by SteveDB1 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
    'Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & ActiveCell.Offset(39, 0).Address), Type:=xlFillDefault
    ''horizontal filling: autofill B1:AM1
    'Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & ActiveCell.Offset(0, 37).Address), Type:=xlFillDefault
     
    'autofill B1:AM40
    Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & ActiveCell.Offset(39, 0).Address), Type:=xlFillDefault
    Range(ActiveCell.Address & ":" & ActiveCell.Offset(39, 0).Address).Select
    Selection.AutoFill Destination:=Range("B1" & ":" & ActiveCell.Offset(39, 37).Address), Type:=xlFillDefault


    • Proposed as answer by Chenchen Li Monday, October 3, 2016 4:27 AM
    • Marked as answer by Chenchen Li 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
            'Your code in here
        Next J
       
        'Alternatively can use the following without first assigning the column numbers to variables
        For J = Columns("B").Column To Columns("AM").Column
            'Your code in here
        Next J


    Regards, OssieMac

    • Proposed as answer by Chenchen Li Monday, October 3, 2016 4:27 AM
    • Marked as answer by SteveDB1 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
    'Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & ActiveCell.Offset(39, 0).Address), Type:=xlFillDefault
    ''horizontal filling: autofill B1:AM1
    'Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & ActiveCell.Offset(0, 37).Address), Type:=xlFillDefault
     
    'autofill B1:AM40
    Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & ActiveCell.Offset(39, 0).Address), Type:=xlFillDefault
    Range(ActiveCell.Address & ":" & ActiveCell.Offset(39, 0).Address).Select
    Selection.AutoFill Destination:=Range("B1" & ":" & ActiveCell.Offset(39, 37).Address), Type:=xlFillDefault


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