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
– samimvp, Commented Dec 14, 2012 at 16:25

Zenadix · Accepted Answer · 2017-09-06 01:36:05Z

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 6, 2017 at 1:36

answered Sep 16, 2014 at 15:54

Zenadix

16.4k5 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RBT Over a year ago

cell variable is an object reference so Set statement must be used during assignment else it will throw this error. Know more about Set keyword here

Asger Over a year ago

@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.

nutsch · Accepted Answer · 2012-12-14 16:43:48Z

0

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

answered Dec 14, 2012 at 16:43

nutsch

5,9622 gold badges23 silver badges36 bronze badges

1 Comment

samimvp Over a year ago

solved, i knew how to get the cell of every cell in the loop by Cells(rowVariable.Row, 42). thanks for help

Collectives™ on Stack Overflow

loop on the rows in excel vba and get cell

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related