Returning the row number of a cell in a range that matches a given condition

  • Thread starter Thread starter Hennie
  • Start date Start date
H

Hennie

Any assistance would be welcomed and much appreciated! I use the code
below to attempt to return the row number of a cell that matches
certain conditions. I have tried several improvisations on the code,
but it fails to return the row number of the cell that matches the
conditions. As a check I have put in the message box, but the value of

the row number stays 0.


Private Sub btnSelect_Click()


Dim caseSelectNr As Long
Dim allCasesCell As Range
Dim allCasesRange As Range
Dim caseCell As Range
Dim rowNum As Long


caseSelectNr = Worksheets("Sheet2").Range("A2").Value


Set allCasesCell = Worksheets("Sheet1").Range("A10000").End(xlUp)
Set allCasesRange = Worksheets("Sheet1").Range("A2", allCasesCell)


On Error Resume Next
If Not allCasesRange Is Nothing Then
For Each caseCell In allCasesRange
If caseCell.Value = tbCaseSelect.Value Then
If caseCell.Value <> caseCell.Offset(1, 0).Value Then
rowNum = caseCell.Row
End If
End If
Exit For
Next caseCell
End If


MsgBox rowNum


End Sub


Any help would be appreciated.
Regards,
Hennie
 
You have an exit for before the next statement and this only does the first
row.

You need to put the exit for after the
rowNum = caseCell.Row

or use
if rowNum <> 0 then exit for
where the current exit for is.
 
Back
Top