how to loop through rows columns in excel VBA Macro

excel, loops, vba

Solution

Here is my sugestion:

Dim i As integer, j as integer

With Worksheets("TimeOut")
    i = 26
    Do Until .Cells(8, i).Value = ""
        For j = 9 to 100 ' I do not know how many rows you will need it.'
            .Cells(j, i).Formula = "YourVolFormulaHere"
            .Cells(j, i + 1).Formula = "YourCapFormulaHere"
        Next j

        i = i + 2
    Loop
 End With

Problem

Hi, I am trying to create a macro that has a loop which copies a function down column 1 (VOL) and another function down column 2 (CAPACITY) for each Station. This is what I have so far: ``` Sub TieOut() Dim i As Integer Dim j As Integer For i = 1 To 3 For j = 1 To 3 Worksheets("TieOut").Cells(i, j).Value = "'=INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&TEXT('INDEX-MATCH'!$A9,"m/dd/yyyy"),'ZaiNet Data'!$C$1:$C$39038,0), 4)" Next j Next i End Sub ``` The picture of what I WANT is below: You can see that I have manually copied and pasted my two functions down each column. I just need a macro that can loop through it. The function I want to be looped down the VOL column for each Station is: ``` =INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C$1:$C$39038,0), 4) ``` The function I want to be looped down the CAPACITY column for each Station is: ``` =INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C$1:$C$39038,0), 5) ``` Could someone please help? Thank you! UPDATE ****How can I make the loop run automatically without having to manually enter the formula into the first two cells and click on macro? Also how can I make the loop run through all the columns/rows? (horizontically)**** I included two screen shots to show what I mean. Below is my current code. Thanks! ``` Sub Loop3() Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste ActiveCell.Offset(-1, 1).Select Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste ActiveCell.Offset(0, -1).Select Dim i As Integer Dim j As Integer With Worksheets("Loop") i = 1 Do Until .Cells(10, i).Value = "blank" For j = 1 To 10 .Cells(j, i).Formula = "=INDEX('ZAINET DATA'!$A$1:$H$39038,MATCH(Loop!E$7&TEXT(Loop!$A9,""M/D/YYYY""),'ZAINET DATA'!$C$1:$C$39038,0),4)" .Cells(j, i + 1).Formula = "=INDEX('ZAINET DATA'!$A$1:$H$39038,MATCH(Loop!E$7&TEXT(Loop!$A9,""M/D/YYYY""),'ZAINET DATA'!$C$1:$C$39038,0),5)" Next j i = i + 2 Loop End With Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste ActiveCell.Offset(-1, 1).Select Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste ActiveCell.Offset(0, -1).Select End Sub ```

Original source