如何在excel VBA宏中循环遍历行列 [英] how to loop through rows columns in excel VBA Macro

问题描述

我试图创建一个循环，它将一个功能从第1列（VOL）和另一个功能下降到第2列（CAPACITY）为每个站。这是我迄今为止所做的：

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.

alt text http://i26.tinypic.com/nz4lfn.jpg

我想要循环的VOL列的功能对于每个站是：

=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)

我想要将每个工作站的CAPACITY列循环的功能是：

=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!

更新

****如何使循环自动运行，而无需手动将公式输入前两个单元格并单击宏？

还如何使循环遍历所有列/行？ （水平地）****

****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)****

我包括两个屏幕截图来显示我的意思。以下是我当前的代码。
alt text http://i26.tinypic.com/i3gw9g.jpg
alt text http://i29.tinypic.com/8izl.jpg
谢谢！

I included two screen shots to show what I mean. Below is my current code. alt text http://i26.tinypic.com/i3gw9g.jpg alt text http://i29.tinypic.com/8izl.jpg 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

推荐答案

这是我的意思：

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

这篇关于如何在excel VBA宏中循环遍历行列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！