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

[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

 

[Bob Phillips]

Try this

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
Exit For
End If
End If
Next caseCell
End If

MsgBox rowNum

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Hennie]

Thanks Bob, It works like a charm! Just a bit of logic, isn't it?

 

[Bob Phillips]

Isn't it always <G>?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)