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


"ksnapp " wrote in message
...
Hi, i need to make a sub find the row number of a cell that countains a
specific value. This is the first part what is going to be a long
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.


---
Message posted from http://www.ExcelForum.com/