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

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

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

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.


--
HTHs Martin


"Hennie" wrote:

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