CODEWITHSUNDEEP
vbaexcel
Luuklag
Luuklag

Reputation: 3914

How to get the row number of the cell in a for each loop

I have a for each loop like this:

Dim rCell as range
Dim Searchrange as range
Set Searchrange = wss.Range("B12:B36") 'where wss is a defined worksheet
For Each rCell in SearchRange
    'do some stuff
    'get the row number of the worksheetrow rCell is currently refering to
Next rCell

How do I get the row number from the original worksheet (wss.) that rCell is currently holding the value of?

Upvotes: 1

Views: 10851

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

There are 2 different "row numbers" I can imagine:

  1. Absolute row number in the sheet
  2. Row number relative to the Searchrange

Example:

Dim SearchRange As Range
Set SearchRange = wss.Range("B12:B36") 'where wss is a defined worksheet

Dim rCell As Range
For Each rCell in SearchRange
    'Absolute row number in the worksheet
    Debug.Print rCell.Row                          'returns 12, 13, 14, … 36

    'Row number relative to the SearchRange
    Debug.Print rCell.Row - Searchrange.Row + 1    'returns 1, 2, 3, … 25
Next rCell

Upvotes: 2

Related Questions