PDA

View Full Version : VBA Code to search for value in worksheet and enter date into next column



Panda
08-23-2014, 03:48 AM
Hi All,

I wonder if anyone can help me. I need some code to seach a value stored within Worksheet 1 in Worksheet 2. Once the value (which will be a mixture of numbers and special characters) is found in worksheet 2, it will enter in the current date and time into the next column.

Worksheet 2 has lots of data so I am guessing it would be a loop of some kind.

Can anyone help me?

Thanks


Phil

GarysStudent
08-23-2014, 05:26 AM
Select a cell on the first worksheet and run:


Sub DateMarker()
Dim v As Variant
v = ActiveCell.Value
Sheets("Sheet2").Activate
Range("A1").Select
Set r = Cells.Find(What:=v, After:=ActiveCell)
If r Is Nothing Then
MsgBox "Value not found"
Exit Sub
Else
r.Offset(0, 1).Value = Now
End If
End Sub




Change the name of the second sheet to suit your needs.

Panda
08-23-2014, 07:31 AM
Select a cell on the first worksheet and run:


Sub DateMarker()
Dim v As Variant
v = ActiveCell.Value
Sheets("Sheet2").Activate
Range("A1").Select
Set r = Cells.Find(What:=v, After:=ActiveCell)
If r Is Nothing Then
MsgBox "Value not found"
Exit Sub
Else
r.Offset(0, 1).Value = Now
End If
End Sub




Change the name of the second sheet to suit your needs.

Thanks for getting back to me so quickly, it works a treat.

Phil

GarysStudent
08-23-2014, 08:19 AM
Thanks for the feedback!