Hi, I have a workbook with a lot of worksheets. I need to write a macro that:
a) takes an input of date in (dd.mm.yyyy) format.
b) takes an input of some txt
c) Search each and every worksheet column F to find that spesific date
d) If such date is present, write the text input to column A at the same row
where the the date has found.
Can anyone help me to solve this problem?
THank you for your help
E.C.

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





"___Zoom" wrote:

> Hi, I have a workbook with a lot of worksheets. I need to write a macro that:
> a) takes an input of date in (dd.mm.yyyy) format.
> b) takes an input of some txt
> c) Search each and every worksheet column F to find that spesific date
> d) If such date is present, write the text input to column A at the same row
> where the the date has found.
> Can anyone help me to solve this problem?
> THank you for your help
> E.C.

Thank you very much.

"OssieMac" wrote:

> 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
>
>
>
>
>
> "___Zoom" wrote:
>
> > Hi, I have a workbook with a lot of worksheets. I need to write a macro that:
> > a) takes an input of date in (dd.mm.yyyy) format.
> > b) takes an input of some txt
> > c) Search each and every worksheet column F to find that spesific date
> > d) If such date is present, write the text input to column A at the same row
> > where the the date has found.
> > Can anyone help me to solve this problem?
> > THank you for your help
> > E.C.

Hi OssieMac,
I received an error message on the:
ws.Select
line of your code.
Maybe my version of Excel 2000 did'nt like the method of selecting the
worksheets...
OK. Can we modify your code such that the selection, control and inputting
process is done in a way something like this?
'--------------
For i=8 to 40
Worksheets.("Sheet"&i).Select
......
.....
'the rest of your code here
Netx
'and the remaining of the code

Thank you for your time and efforts
___Zoom




"OssieMac" wrote:

> 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
>
>
>
>
>
> "___Zoom" wrote:
>
> > Hi, I have a workbook with a lot of worksheets. I need to write a macro that:
> > a) takes an input of date in (dd.mm.yyyy) format.
> > b) takes an input of some txt
> > c) Search each and every worksheet column F to find that spesific date
> > d) If such date is present, write the text input to column A at the same row
> > where the the date has found.
> > Can anyone help me to solve this problem?
> > THank you for your help
> > E.C.

Hi
By the way,
the macro also did not accept the command
SearchFormat:=False
as well. But I removed it and skipped that step.
___Zoom


"OssieMac" wrote:

> 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
>
>
>
>
>
> "___Zoom" wrote:
>
> > Hi, I have a workbook with a lot of worksheets. I need to write a macro that:
> > a) takes an input of date in (dd.mm.yyyy) format.
> > b) takes an input of some txt
> > c) Search each and every worksheet column F to find that spesific date
> > d) If such date is present, write the text input to column A at the same row
> > where the the date has found.
> > Can anyone help me to solve this problem?
> > THank you for your help
> > E.C.