如何使用 Excel VBA 宏循环行?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/4956739/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 11:09:01  来源:igfitidea点击:

How to loop rows with Excel VBA macro?

excelvbaloopsexcel-vba

提问by Jeff

I am new to VBA, but pretty good with PHP. That being said, I'm struggling with VBA loops...

我是 VBA 新手,但对 PHP 非常好。话虽如此,我正在为 VBA 循环而苦苦挣扎......

I have this sheet with 40 rows called "SH1":

我有一张名为“SH1”的 40 行工作表:

SH1

     A     B     C     D     E
 1   2   One    1.0a   12
 2   7   Two    2.0b   34
 3  13   Three  3.0c   56
 4  14   Four   4.0d   78
..
40

I need to loop through 40 rows and check the value in column A. If the value in column A meets my criteria (see below), generate some output and put it in another sheet.

我需要遍历 40 行并检查 A 列中的值。如果 A 列中的值符合我的条件(见下文),则生成一些输出并将其放入另一个工作表中。

My output sheet is 3-columns and called "SH2":

我的输出表是 3 列,称为“SH2”:

SH2

     A     B     C     D     E
 1  1.0a   12    One
    2.0b   34    Two
 2  3.0c   56    Three
    4.0d   78    Four
..
15

My criteria for deciding what goes where:

我决定什么去哪里的标准:

// First loop:
if a1 < 8, put c1 in SH2 a1, put d1 in SH2 b1, put b1 in SH2 c1
if a2 < 8, put c2 in SH2 a1, put d2 in SH2 b1, put b2 in SH2 c1
// ... loop through a40 ...

Then:

然后:

// Second loop:
if a1 > 8 AND a1 < 16, put c1 in SH2 a2, put d1 in SH2 b2, put b1 in SH2 c2
if a2 > 8 AND a2 < 16, put c2 in SH2 a2, put d2 in SH2 b2, put b2 in SH2 c2
// ... loop through a40 ...

PROGRESS EDIT:

进度编辑:

Seems to be working, but wondering if there is a "cleaner" way?

似乎有效,但想知道是否有“更清洁”的方法?

Sub CatchersPick2()
    Dim curCell As Range

    For Each curCell In Sheet4.Range("C3:C40").Cells
        If curCell.Value > 0 And curCell.Value < 73 Then
            cLeft = cLeft _
                & curCell.Offset(0, 5) & "." _
                & curCell.Offset(0, 6) & vbLf
            cMidl = cMidl _
                & curCell.Offset(0, -2) & ", " _
                & curCell.Offset(0, -1) & " " _
                & curCell.Offset(0, 7) & vbLf
            cRght = cRght _
                & curCell.Offset(0, 9) & " " _
                & curCell.Offset(0, 2) & " " _
                & curCell.Offset(0, 11) & " " _
                & curCell.Offset(0, 10) & vbLf
        End If
    Next curCell

    Sheet6.Range("B3") = cLeft
    Sheet6.Range("C3") = cMidl
    Sheet6.Range("D3") = cRght
    Sheet6.Range("B3:D3").Rows.AutoFit
    Sheet6.Range("B3:D3").Columns.AutoFit

End Sub

采纳答案by jswolf19

Dim cell As Range
For Each cell In Range("a1:a40")
    'do stuff here
Next cell

You can get your current row with cell.Row. Good luck ^_^

您可以使用cell.Row. 祝你好运^_^

回答by Fionnuala

How about:

怎么样:

Sub Catchers()
    Dim cell As Range

    Sheet1.Select 'SHEET: C

    For Each cell In Range("C3:C40")
        If cell.Value < 35 And cell.Value > 0 Then
            With Sheet6
                .Range("B" & cell.Row) = cell.Offset(0, 5) _
                    & "." & cell.Offset(0, 6)

                .Range("C" & cell.Row) = cell.Offset(0, -2) _
                    & ", " & cell.Offset(0, -1) _
                    & " " & cell.Offset(0, 7)

                .Range("D" & cell.Row) = cell.Offset(0, 9) _
                    & " " & cell.Offset(0, 2) _
                    & " " & cell.Offset(0, 11) _
                    & " " & cell.Offset(0, 10)
            End With
        End If
    Next cell

    Sheet6.Range("B4:D4").Rows.AutoFit
    Sheet6.Range("B4:D4").Columns.AutoFit

End Sub

回答by Lance Roberts

There's not a lot you can do, but...

你能做的不多,但...

First, don't use the word 'cell' as a variable, it may work, but it's playing with fire, so

首先,不要使用“细胞”这个词作为变量,它可能有用,但它是在玩火,所以

Dim curCell as Range

Second, you should loop through the Cells property of the Range

其次,您应该遍历 Range 的 Cells 属性

For Each curCell In Range("C3:C40").Cells

Third, you don't need to Select the cell, you can just manipulate the curCell variable

三、不需要Select cell,直接操作curCell变量即可

Lastly, you won't need to use ActiveCell, just use the curCell variable.

最后,您不需要使用 ActiveCell,只需使用 curCell 变量即可。

If curCell.Value < 35 And curCell.Value > 0 Then

    cLefta = curCell.Offset(0, 5) & "."

In fact, you could also just use a short variable like 'c' and put the whole thing on one line:

事实上,您也可以只使用像 'c' 这样的短变量并将整个内容放在一行中:

cLeft = c.Offset(0,5) & "." & c.Offset(0,6) & vblf

Note: If your setup is close to the same every time, it would probably be easier to just use worksheet-functions.

注意:如果您的设置每次都接近于相同,那么仅使用工作表功能可能会更容易。