I need the number of a row that meets my criteria

[ksnapp]

Hi, i need to make a sub find the row number of a cell that countains
specific value. This is the first part what is going to be a lon
function and this will help me define ranges.

here is the code.


Sub freq()
Dim IndexColumnlastRow As Long
Dim TimeOfDayToFind As String
Dim TimeOfDaysRowNumber As Single

Worksheets("frequency").Select

With Worksheets("frequency")
Range("b3").Select

IndexColumnlastRow = Cells(Rows.Count, "a").End(xlUp).Row

TimeOfDayToFind = ActiveCell.Offset(-1, 0).Value

End With

With Worksheets("zscore")

Range("a1:a16").Select
For Each cell In Selection
If cell.Value = TimeOfDayToFind Then
cell.Select
TimeOfDaysRowNumber = Application.WorksheetFunction.Row(cell)
End If
Next

End With

MsgBox IndexColumnlastRow
MsgBox TimeOfDayToFind
MsgBox TimeOfDaysRowNumber

End Sub

Any assistance is appreciated

 

[Trevor Shuttleworth]

Try this:

Sub freq2()
Dim cell As Range
Dim IndexColumnlastRow As Long
Dim TimeOfDayToFind As String
Dim TimeOfDaysRowNumber As Single

With Worksheets("frequency")
IndexColumnlastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
With .Range("b3")
TimeOfDayToFind = .Offset(-1, 0).Value
End With
End With

With Worksheets("zscore")
For Each cell In Range("a1:a16")
If cell.Value = TimeOfDayToFind Then
TimeOfDaysRowNumber = cell.Row
Exit For
End If
Next
End With

MsgBox IndexColumnlastRow
MsgBox TimeOfDayToFind
MsgBox TimeOfDaysRowNumber

End Sub

If the date is not found, the row will be zero as you haven't given it a
value. You will need to test for that in subsequent routines.

But why not:

TimeOfDayToFind = .Range("b2").Value

instead of:

With .Range("b3")
TimeOfDayToFind = .Offset(-1, 0).Value
End With


Regards

Trevor

 

[Don Guillett]

something like this maybe. UNTESTED but NO selections

tod=Worksheets("frequency").cells(Rows.Count, "a").End(xlUp).Row-1
msgbox Worksheets("zscore").columns(1).find(tod).row