如何使用Excel VBA宏循环行？ [英] How to loop rows with Excel VBA macro?

问题描述

我是VBA的新手，但是很好用PHP。就是说，我正在努力与VBA循环...

我有这个表格有40行称为SH1：

  SH1 
 
 ABCDE 
 1 2一个1.0a 12 
 2 7两个2.0b 34 
 3 13三个3.0c 56 
 4 14四4.0d 78 
 .. 
 40 
  

我需要循环40行并检查列A中的值。如果列A中的值满足我的条件（见下文），请生成一些输出并将其放在另一个表中。

我的输出表是3列，称为SH2：

  SH2 
 
 ABCDE 
 1 1.0a 12一
 2.0b 34二
 2 3.0c 56三
 4.0d 78四
 .. 
 15 
  

我的条件决定了哪里：

  //第一个循环：
如果a1< 8，将c1置于SH2 a1中，将d1置于SH2 b1中，将b1置于SH2 c1 
中，如果a2 < 8，将c2放在SH2 a1中，将d2放在SH2 b1中，将b2放在SH2中c1 
 // ...循环通过a40 ... 
  

然后：

  //第二个循环：
如果a1 > 8 AND a1 < 16，将c1置于SH2 a2中，将d1置于SH2 b2中，将b1置于SH2 c2 
中，如果a2> 8 AND a2 < 16，将c2放在SH2 a2中，将d2放在SH2 b2中，将b2放在SH2中c2 
 // ...循环通过a40 ... 
  

进度编辑：

似乎要工作，但是想知道是否有更清洁的方式？

  Sub CatchersPick2（）
 Dim curCell As Range 
 
 For Each curCell In Sheet4.Range（C3：C40）。单元格
如果curCell.Value> 0和curCell.Value< 73然后
 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 
下一个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 
  

解决方案

  Dim cell As Range 
对于每个单元格的范围（a1：a40）
'这里的东西
下一个单元格
  pre> 
 
 
您可以使用 cell.Row 获取当前行。祝你好运^ _ ^ 
 
I am new to VBA, but pretty good with PHP. That being said, I'm struggling with VBA loops...

I have this sheet with 40 rows called "SH1":
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.

My output sheet is 3-columns and called "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

解决方案
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 ^_^

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