here is the code



Sub dater3()
Dim lr As Single
Dim r As Variant
Dim Sr As Long
r = Range("a1").Value

r = Day(r)
MsgBox (r)

Windows("Navy Cash stats April.xls").Activate
Sheets(r).Select

With ActiveSheet

lr = Cells(Rows.Count, 1).End(xlUp).Row

Range("a1", Cells(lr, 1)).Select
For Each cell In Selection
If cell.Value = "7:00" Then
cell.Select
Sr = cell.Row

End If
Next
End With

MsgBox (Sr)

End Sub

the thing is that the last message box comes back zero
there is a cell in that range that says "7:00" its number is 16.
think that the format of X:XX is messin it all up. I can't figure
way around this.


Help Pleas

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

I'm thinking that the 7:00 is being treated as time in
Excel and what you see on the screen is not what it's
storing and using for a value. If you don't want to use
time, try using a different format by putting a quote in
front or changing the : to something else.

If time is OK, then try changing the "If Cell.Value..."
line to read:

If Format(Cell.Value,"H:MM") = "7:00" Then

Dan
-----Original Message-----
here is the code



Sub dater3()
Dim lr As Single
Dim r As Variant
Dim Sr As Long
r = Range("a1").Value

r = Day(r)
MsgBox (r)

Windows("Navy Cash stats April.xls").Activate
Sheets(r).Select

With ActiveSheet

lr = Cells(Rows.Count, 1).End(xlUp).Row

Range("a1", Cells(lr, 1)).Select
For Each cell In Selection
If cell.Value = "7:00" Then
cell.Select
Sr = cell.Row

End If
Next
End With

MsgBox (Sr)

End Sub

the thing is that the last message box comes back zero
there is a cell in that range that says "7:00" its number
is 16. I
think that the format of X:XX is messin it all up. I
can't figure a
way around this.


Help Please


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

.

you are correct. Time is saved in a cell as the decimal pat of a day So 8AM
is 0.333 , then the "day" value of the date is zero. .

--
Patrick Molloy
Microsoft Excel MVP

"Dan Frederick" wrote in message
...
I'm thinking that the 7:00 is being treated as time in
Excel and what you see on the screen is not what it's
storing and using for a value. If you don't want to use
time, try using a different format by putting a quote in
front or changing the : to something else.

If time is OK, then try changing the "If Cell.Value..."
line to read:

If Format(Cell.Value,"H:MM") = "7:00" Then

Dan
-----Original Message-----
here is the code



Sub dater3()
Dim lr As Single
Dim r As Variant
Dim Sr As Long
r = Range("a1").Value

r = Day(r)
MsgBox (r)

Windows("Navy Cash stats April.xls").Activate
Sheets(r).Select

With ActiveSheet

lr = Cells(Rows.Count, 1).End(xlUp).Row

Range("a1", Cells(lr, 1)).Select
For Each cell In Selection
If cell.Value = "7:00" Then
cell.Select
Sr = cell.Row

End If
Next
End With

MsgBox (Sr)

End Sub

the thing is that the last message box comes back zero
there is a cell in that range that says "7:00" its number
is 16. I
think that the format of X:XX is messin it all up. I
can't figure a
way around this.


Help Please


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

.