Hi Zoom,
NOTE: Make a backup copy of your workbook in case the macro does not do what
you expect.
The macro below that should do what you want. However, at the input you
enter your date as d/mm/yyyy (with '/' between values). If you try to enter
it as dd.mm.yyyy with the dots then VBA thinks it is a time. It does not
matter how you have your dates formatted in the worksheet because it is
finding a date and VBA knows what it is.
I assume that you know how to copy the macro into the VBA editor. If not
then get back to me.
If you have more than one date in any sheet then it writes the text to
column A for all occurrences of the date.
Sub Find_Dates()
Dim inputDate As Date
Dim inputTxt As String
Dim ws As Worksheet
Dim rngF As Range
Dim c As Range
Dim firstAddress As String
Dim foundDate As Boolean
On Error GoTo BadDate
inputDate = InputBox("Enter the Date to be found" _
& Chr(13) & "format d/m/yyyy")
On Error GoTo 0
inputTxt = InputBox("Enter the required text")
If inputTxt = "" Then
MsgBox "No text entered. Processing terminated"
End
End If
foundDate = False
For Each ws In ThisWorkbook.Sheets
ws.Select
Set rngF = Range("F1", Cells(Rows.Count, 6).End(xlUp))
firstAddress = "" 'Initialize
Set c = rngF.Find(What:=inputDate, _
After:=rngF.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not c Is Nothing Then
foundDate = True
firstAddress = c.Address
Do
Cells(c.Row, 1) = inputTxt
Set c = rngF.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next ws
'Remove the next 3 lines if you do not want
'the message when the date is not found
If foundDate = False Then
MsgBox "Date " & inputDate & " not found any sheet"
End If
End
BadDate:
MsgBox "Invalid date entry. Processing terminated"
End
End Sub
Hope it works for you.
Regards,
OssieMac
