Hello akrp10,
I assume that you have a .xls file that contains two worksheets.
The worksheet 'Source' contains a cell that has a date value.
You need to locate the cell in the other worksheet ('Destination') that contains the same date.
To do this, select the cell that contains the date to be serched and run the following VBA code:
'--------
Sub FindInDest()
Dim valueToFind, foundAt, sourceSheetName
' locates the cell in the other worksheet
' that contains the same value as inthe
' selected cell.
'
' Code by SanganakSakha, 4/8/01
' No comprehensive error checking done
valueToFind = Selection
sourceSheetName = ActiveSheet.Name
ThisWorkbook.Sheets("destination").Select
ActiveSheet.Cells.Select
foundAt = Selection.Find(What:=valueToFind, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Address
ThisWorkbook.Sheets(sourceSheetName).Select
MsgBox foundAt
End Sub
'------
To be run this code,
1) Copy this code to clipboard.
2) Open the excel file.
3) Open Visual Basic Editor.
4) Paste the code in the code window for the sheet 'Source'.
5) Close the VBE.
6) Select the cell that contains the required date.
7) Tools > Macro > Macros...
8) select 'FindInDest' and click run.
The message box will show the address of the cell that contains the same value (any type, not restricted to date).The variable foundAt also contains thios value which you can use in your code.
I have successfully tried this solution and would be glad to provide any further help you may need to implement this solution.
With best compliments,
SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.