Please help with VBA. Insert date into cell in a particular row.

[coordintor]

Hello,

I'm a new on forum. I need your help with creating VBA for button which will insert the date in particular cell in a row found by specyfied value)).
To put it simple, below is my tab.

	a	b	c	d	e	f
1
2			content	f
3
4		column1	column2	column3	column4	column5
5		xxxx	b	yyyy	zzzz
6		xxxx	c	yyyy	zzzz
7		xxxx	f	yyyy	zzzz	15-06-09
8		xxxx	g	yyyy	zzzz
9		xxxx	h	yyyy	zzzz
10		xxxx	a	yyyy	zzzz

<tbody>
</tbody>

<tbody>
</tbody>

The logic is follow:
1. The value in D2 is changed
2. By means of button I need to insert and save the actual date in column5(F) if value in colum2(C) equals to D2.
3. If not found - message "not Found"

Thank you in advance for any help.
Please note that I'm not good in VBA

 

[mumps]

You can do this using a formula. Enter this formula in cell F5 and copy it down as far as your need it:=IF(C5=$D$2,NOW(),"Not found")
If you want to use a macro:

Sub EnterDate()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("C5:C" & LastRow)
        If rng = Range("D2") Then
            Range("F" & rng.Row) = Date
        Else
            Range("F" & rng.Row) = "Not found"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[coordintor]

Hi Mumps,

Thank you for quick response. I cannot use the formula as I need to keep the date after D2 is changed to other possible value. I gonna try your macro and get back to you with result. Thanks

 

[ranman256]

Sub FindAndDate()
Dim vVal
On Error GoTo errSkip


vVal = InputBox("Enter Value", "set date on value")


    Columns("C:C").Select
    Selection.Find(What:=vVal, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
Range(ActiveCell.Address).Select
activecell.offset(0,3).value = date()


Exit Sub


errSkip:
Range("C1").Select
MsgBox "Not Found"
End Sub

 

[coordintor]

You can do this using a formula. Enter this formula in cell F5 and copy it down as far as your need it:=IF(C5=$D$2,NOW(),"Not found")
If you want to use a macro:

Sub EnterDate()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("C5:C" & LastRow)
        If rng = Range("D2") Then
            Range("F" & rng.Row) = Date
       [B] Else
            Range("F" & rng.Row) = "Not found"[/B]        
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Hi Mumps,
Macro works good but "Else" logic is not what I expected. I need to see the message box with "Not found"
Could you help with this?