loop on the rows in excel vba and get cell

Question

I need help in Excel. My question is: How can I get the cell 42 of each row in this loop? As:

For Each r In Sheets("Sheet2").UsedRange.Rows
     sval = r.Cells(42)

     If sval = "" Then
         If r.Cells(6).Value <> "" And r.Cells(7).Value <> "" And r.Cells(9).Value <> "" And r.Cells(10).Value <> "" And r.Cells(11).Value <> "" And r.Cells(12).Value <> "" Then
             MsgBox "wtehi"
             r.EntireRow.Interior.ColorIndex = 0
         Else
             MsgBox "yallow"
             emptyMand = "ok"
             r.EntireRow.Interior.ColorIndex = 6
         End If
     End If

Next

i wanna loop for every used rows in the sheet and want to get the cells and values of column 42 for every row as in as the above , i wanna to see the value of column is empty then wanna to see if the cell 6 7 9 10 11 12 are empty then make the color for the row white else make it yallow — samimvp, Dec 14 '12 at 16:25

Zenadix · Answer 1 · 2017-09-06T01:36:05.877

4

To loop through all rows of worksheet ws and, for each row, get the cell on column 42, you can do this:

For Each rw in ws.UsedRange.Rows
    cell = ws.Cells(rw.Row, 42)
Next

However, the method below is twice as fast, and more readable:

For i = 1 to ws.UsedRange.Rows.Count
    cell = ws.Cells(i, 42)
Next

edited Sep 06 '17 at 01:36

answered Sep 16 '14 at 15:54

Zenadix

15,291
4
26
41

`cell` variable is an object reference so `Set` statement must be used during assignment else it will throw [this](https://stackoverflow.com/q/20692280/465053) error. Know more about `Set` keyword [here](https://stackoverflow.com/q/349613/465053) – RBT Apr 03 '18 at 11:57
@RBT If OP wants to reference a cell as `Range` object, then you're right, but he wanted to reference the value, so in this case `cell = ws.Cells(i, 42).Value` would have clarified it. – Asger Jun 24 '19 at 07:26

score 0 · Answer 2 · answered Dec 14 '12 at 16:43

Another recommended non-macro option being of course to use conditional formatting, I'd suggest this for the macro part:

Dim cl As Range
For Each cl In Intersect(Sheets("Sheet2").UsedRange, Sheets("Sheet2").Columns(42))

     If Len(cl) = 0 Then
         If Application.WorksheetFunction.CountIf(Cells(cl.Row, 6).Resize(1, 5), "") <> 5 Then
             MsgBox "wtehi"
             cl.EntireRow.Interior.ColorIndex = 0
         Else
             MsgBox "yallow"
             emptyMand = "ok"
             cl.EntireRow.Interior.ColorIndex = 6
         End If
     End If

Next cl

solved, i knew how to get the cell of every cell in the loop by Cells(rowVariable.Row, 42). thanks for help — samimvp, Dec 16 '12 at 12:20

loop on the rows in excel vba and get cell

2 Answers2