title: how to loop through rows columns in excel VBA Macro
body: <p><strong>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:</strong></p>
  
  <pre><code>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&amp;TEXT('INDEX-MATCH'!$A9,"m/dd/yyyy"),'ZaiNet Data'!$C$1:$C$39038,0), 4)"
          Next j
      Next i
  
  End Sub
  </code></pre>
  
  <p><strong>The picture of what I WANT is below:</strong> 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.</p>
  
  <p><a href="https://i.stack.imgur.com/3Okno.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3Okno.jpg" alt="alt text"></a>
  </p>
  
  <p><strong>The function I want to be looped down the VOL column for each Station is:</strong></p>
  
  <pre><code>=INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&amp;TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C$1:$C$39038,0), 4)
  </code></pre>
  
  <p><strong>The function I want to be looped down the CAPACITY column for each Station is:</strong></p>
  
  <pre><code>=INDEX('ZaiNet Data'!$A$1:$H$39038,MATCH('INDEX-MATCH'!Z$7&amp;TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C$1:$C$39038,0), 5)
  </code></pre>
  
  <p>Could someone please help? Thank you!</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>****How can I make the loop run automatically without having to manually enter the formula into the first two cells and click on macro?<br>
  Also how can I make the loop run through all the columns/rows? (horizontically)****</p>
  
  <p>I included two screen shots to show what I mean. Below is my current code.
  <a href="https://i.stack.imgur.com/Ua6rM.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ua6rM.jpg" alt="alt text"></a>
  
  <a href="https://i.stack.imgur.com/3YpsG.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3YpsG.jpg" alt="alt text"></a>
  
  Thanks!</p>
  
  <pre><code>    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&amp;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&amp;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
  </code></pre>