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.

Try this:-

Alt+F11, insert module and paste it in

Sub versive_Element()
mydate = Range("A1").Value
mytext = Range("A2").Value
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Range("F1:F10").Select
For Each c In Selection
c.Select
If c.Value = mydate Then
ActiveCell.Offset(0, -5).Value = mytext
End If
Next c
Next ws
End Sub

It will pick up the date and text from A1 and A2 on the sheet it is called
from

Mike


"___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 Mike...I'll try it and get back to here again to comment.
___Zoom

"Mike H" wrote:

Try this:-

Alt+F11, insert module and paste it in

Sub versive_Element()
mydate = Range("A1").Value
mytext = Range("A2").Value
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Range("F1:F10").Select
For Each c In Selection
c.Select
If c.Value = mydate Then
ActiveCell.Offset(0, -5).Value = mytext
End If
Next c
Next ws
End Sub

It will pick up the date and text from A1 and A2 on the sheet it is called
from

Mike


"___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.

When execute the macro, I received an error message:

Run-time error '1004'
method 'Select' of object'-Worksheet' failed

on the line
ws.Select

What have I done wrong?
___Zoom


"Mike H" wrote:

Try this:-

Alt+F11, insert module and paste it in

Sub versive_Element()
mydate = Range("A1").Value
mytext = Range("A2").Value
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Range("F1:F10").Select
For Each c In Selection
c.Select
If c.Value = mydate Then
ActiveCell.Offset(0, -5).Value = mytext
End If
Next c
Next ws
End Sub

It will pick up the date and text from A1 and A2 on the sheet it is called
from

Mike


"___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,

I can't replicate that error but maybe it's to do with where you put the
code. It goes in a workbook module.

Alt + F11 to open VB editor
Right click This Workbook and then insert module
The newly inserted module should be selected
paste the code in exactly as provided.

Call it from a worksheets and it will take A1 & A2 values from that sheet to
use as the sate and text.

Mike

"___Zoom" wrote:

When execute the macro, I received an error message:

Run-time error '1004'
method 'Select' of object'-Worksheet' failed

on the line
ws.Select

What have I done wrong?
___Zoom


"Mike H" wrote:

Try this:-

Alt+F11, insert module and paste it in

Sub versive_Element()
mydate = Range("A1").Value
mytext = Range("A2").Value
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Range("F1:F10").Select
For Each c In Selection
c.Select
If c.Value = mydate Then
ActiveCell.Offset(0, -5).Value = mytext
End If
Next c
Next ws
End Sub

It will pick up the date and text from A1 and A2 on the sheet it is called
from

Mike


"___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 Mike,
Done all the things you said but Sorry to say that I get the same error
message.
By te way to call it I just use the module name as:
versive_Element
is that OK?.


"Mike H" wrote:

Hi,

I can't replicate that error but maybe it's to do with where you put the
code. It goes in a workbook module.

Alt + F11 to open VB editor
Right click This Workbook and then insert module
The newly inserted module should be selected
paste the code in exactly as provided.

Call it from a worksheets and it will take A1 & A2 values from that sheet to
use as the sate and text.

Mike

"___Zoom" wrote:

When execute the macro, I received an error message:

Run-time error '1004'
method 'Select' of object'-Worksheet' failed

on the line
ws.Select

What have I done wrong?
___Zoom


"Mike H" wrote:

Try this:-

Alt+F11, insert module and paste it in

Sub versive_Element()
mydate = Range("A1").Value
mytext = Range("A2").Value
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Range("F1:F10").Select
For Each c In Selection
c.Select
If c.Value = mydate Then
ActiveCell.Offset(0, -5).Value = mytext
End If
Next c
Next ws
End Sub

It will pick up the date and text from A1 and A2 on the sheet it is called
from

Mike


"___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.

For the first part of your set of questions, you would just need to record
the macro in the macro player and format cells under custom and type in
dd.mm.yyyy. Hope this helps.

Daren

"___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.

Thanks Daren,


"Daren" wrote:

For the first part of your set of questions, you would just need to record
the macro in the macro player and format cells under custom and type in
dd.mm.yyyy. Hope this helps.

Daren

"___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.