how to loop through rows columns in excel VBA Macro

Question

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 amp 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 amp 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 amp 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 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

User · Answer

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

User · Answer

I d recommend the Range object s AutoFill method for this   rngSource AutoFill Destination  rngDest   Specify the Source range that contains the values or formulas you want to fill down  and the Destination range as the whole range that you want the cells filled to  The Destination range must include the Source range  You can fill across as well as down   It works exactly the same way as it would if you manually  dragged  the cells at the corner with the mouse  absolute and relative formulas work as expected   Here s an example    Set some example values  Range  A1   Value    1  Range  B1   Formula     NOW    Range  C1   Formula     B1 A1    AutoFill the values   formulas to row 20  Range  A1 C1   AutoFill Destination  Range  A1 C20     Hope this helps

User · Answer

Try this   Create A Macro with the following thing inside   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   That particular macro will copy the current cell  place your cursor in the VOL cell you wish to copy  down one row and then copy the CAP cell also   This is only a single loop so you can automate copying VOL and CAP of where your current active cell  where your cursor is  to down 1 row   Just put it inside a For loop statement to do it x number of times  like   For i   1 to 100  Do this 100 times     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 Next i

User · Answer

This one is similar to  Wilhelm s solution   The loop automates based on a range created by evaluating the populated date column   This was slapped together based strictly on the conversation here and screenshots   Please note  This assumes that the headers will always be on the same row  row 8    Changing the first row of data  moving the header up down  will cause the range automation to break unless you edit the range block to take in the header row dynamically   Other assumptions include that VOL and CAPACITY formula column headers are named  Vol  and  Cap  respectively   Sub Loop3    Dim dtCnt As Long Dim rng As Range Dim frmlas   As String  Application ScreenUpdating   False   The following code block sets up the formula output range dtCnt   Sheets  Loop   Range  A1048576   End xlUp  Row               lowest date column populated endHead   Sheets  Loop   Range  XFD8   End xlToLeft  Column          right most header populated Set rng   Sheets  Loop   Range Cells 9  2   Cells dtCnt  endHead     assigns range for automation  ReDim frmlas 1        array assigned to formula strings      VOL column formula frmlas 0     VOL FORMULA       CAPACITY column formula frmlas 1     CAP FORMULA   For i   1 To rng Columns count If rng 0  i  Value    Vol  Then          checks for volume formula column     For j   1 To rng Rows count         rng j  i  Formula  frmlas 0      inserts volume formula     Next j ElseIf rng 0  i  Value    Cap  Then      checks for capacity formula column     For j   1 To rng Rows count         rng j  i  Formula   frmlas 1     inserts capacity formula     Next j End If Next i  Application ScreenUpdating   True  End Sub

[excel] how to loop through rows columns in excel VBA Macro

Examples related to excel

Examples related to vba

Examples related to loops