How can I put a cell location (ie. row and column #, not value in.

[Guest]

I have a date value, 4/1/2005, that I would like to find the cell location of
and put into a variable. Is this possible? I would like to use this
location to calculate other necessary items dynamically as the dates pass.
All I really need, and definitely need, is the row the value is in. If you
have an idea on how to do this it would be most helpful and appreciated.
Thanks!

 

[Guest]

Try this...

Sub FindDate()
Dim wks As Worksheet
Dim rngSearch As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngSearch = wks.Cells
Set rngFound = rngSearch.Find("4/1/2005")

If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
MsgBox rngFound.Address
End If
End Sub

HTH

 

[Stevie_mac]

Not entirely certain what you mean, but I think you need the following functions...
Lookup, VLookup & HLookup.

As an example, put in Sheet2 the following entries (in A1 to B3)...
3/1/2005 third
4/1/2005 forth
5/1/2005 fifth

On sheet1, put =VLookup(A1, Sheet2!A1:C200,2,FALSE) in CELL A2
Now, type 4/1/2005 in Sheet1 A1 - this put the text "forth" in cell A2
Overtype A1 with 5/1/2005 - this will put "fifth" in cell A2

Hope this helps - Steve.

 

[Guest]

Thank you! This is exactly what I needed!

Try this...

Sub FindDate()
Dim wks As Worksheet
Dim rngSearch As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngSearch = wks.Cells
Set rngFound = rngSearch.Find("4/1/2005")

If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
MsgBox rngFound.Address
End If
End Sub

HTH

 

[Guest]

Thanks Jim! That's exactly what I needed!

Try this...

Sub FindDate()
Dim wks As Worksheet
Dim rngSearch As Range
Dim rngFound As Range

Set wks = ActiveSheet
Set rngSearch = wks.Cells
Set rngFound = rngSearch.Find("4/1/2005")

If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
MsgBox rngFound.Address
End If
End Sub

HTH