vba 如何遍历excel VBA宏中的行列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1128841/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to loop through rows columns in excel VBA Macro
提问by Techgirl09
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:
嗨，我正在尝试创建一个具有循环的宏，该循环为每个站复制一个向下列 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:$H038,MATCH('INDEX-MATCH'!Z&TEXT('INDEX-MATCH'!$A9,"m/dd/yyyy"),'ZaiNet Data'!$C:$C038,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:
我想要为每个站的 VOL 列循环的功能是：
=INDEX('ZaiNet Data'!$A:$H038,MATCH('INDEX-MATCH'!Z&TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C:$C038,0), 4)
The function I want to be looped down the CAPACITY column for each Station is:
我想为每个站的 CAPACITY 列循环的功能是：
=INDEX('ZaiNet Data'!$A:$H038,MATCH('INDEX-MATCH'!Z&TEXT('INDEX-MATCH'!$A438,"M/DD/YYYY"),'ZaiNet Data'!$C:$C038,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:$H038,MATCH(Loop!E&TEXT(Loop!$A9,""M/D/YYYY""),'ZAINET DATA'!$C:$C038,0),4)"
.Cells(j, i + 1).Formula = "=INDEX('ZAINET DATA'!$A:$H038,MATCH(Loop!E&TEXT(Loop!$A9,""M/D/YYYY""),'ZAINET DATA'!$C:$C038,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
回答by Wilhelm
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
回答by Jimmy Chandra
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.
该特定宏将复制当前单元格（将光标放在要复制的 VOL 单元格中）向下一行，然后也复制 CAP 单元格。
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.
这只是一个循环，因此您可以自动将当前活动单元格（光标所在位置）的 VOL 和 CAP 复制到下 1 行。
Just put it inside a For loop statement to do it x number of times. like:
只需将它放在 For 循环语句中即可执行 x 次。喜欢：
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
回答by Jomtung
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.
这个类似于@Wilhelm 的解决方案。该循环根据通过评估填充的日期列创建的范围自动执行。这是严格根据此处的对话和屏幕截图拼凑在一起的。
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.
请注意：这假设标题将始终位于同一行（第 8 行）。更改第一行数据（向上/向下移动标题）将导致范围自动化中断，除非您编辑范围块以动态接收标题行。其他假设包括 VOL 和 CAPACITY 公式列标题分别命名为“Vol”和“Cap”。
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
回答by Nossidge
I'd recommend the Range object's AutoFillmethodfor 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 formulaswork 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.
希望这可以帮助。
