How to loop rows with Excel VBA macro?

Question

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

Sorry, but that looks quite messy and does not seem to match the earlier stuff. BTW, you say `if a2 > 8 AND a1 < 16` do you mean if `a2 > 8 AND a2 < 16` (second loop, line 2). Do you want to do the earlier stuff (first, second lopp) ? — Fionnuala, Feb 10 '11 at 16:04
My earlier stuff was just an example built on the bare output I need generated. My example above is working, but being new to VBA loops and variables, I'm sure there's a cleaner way (HELP!). To answer your question, "yes," that was a typo. — Jeff, Feb 10 '11 at 16:06

score 11 · Accepted Answer · answered Feb 10 '11 at 12:06

11

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 ^_^

answered Feb 10 '11 at 12:06

jswolf19

2,303
15
16

Fionnuala · Answer 2 · 2011-02-10T17:57:08.427

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

I don't see any concatenation in there for writing to Sheet6, but that `With Sheet6` statement sure opens my eyes. =) — Jeff, Feb 10 '11 at 17:15
It writes to each row in sheet6 as matched by the row in sheet 1. Try it on a copy. — Fionnuala, Feb 10 '11 at 17:16

Lance Roberts · Answer 3 · 2011-02-10T19:59:43.387

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

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

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

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

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:

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.

Great info, thanks! I used this line for concatenation: `cLeft = cLeft & curCell.Offset(0, 5) & "." & curCell.Offset(0, 6) & vbLf`. Updated my function to reflect the changes. — Jeff, Feb 10 '11 at 17:21

How to loop rows with Excel VBA macro?

3 Answers3

Linked

Related