CODEWITHSUNDEEP
excelvba
Dark161000
Dark161000

Reputation: 31

Add a Value Automatically to a Cell Based on Another Cell

I want to add a value to a cell based on another with VBA but I'm not sure how. I already searched on internet about it but can't find anything.

I have a table, and on the Column C, if any cell contains the text "MAM" (because it might have MAM-565), then change the value from Cell A to "Wrong", but if it contains "NAC", then change value to "Correct". It should be in the same row as the text found.

Also, I want to add the date automatically to cell E every time Cell in D is filled. This the code I have already:

Private Sub Worksheet_Change(ByVal Target As Range)

'Add Issue Type'
Dim Code As Range
Set Code = Range("C2:C100000")

For Each Cell In Code
If InStr(1, Cell, "NAC") Then
Range("A2:A10000").Value = "Correct"
ElseIf InStr(1, Cell, "MAM") Then
Range("A2:A10000").Value = "Wrong"

End If
Next

End Sub

This how my table looks like: Table

Thanks in advance guys :)

Upvotes: 0

Views: 377

Answers (1)

BigBen
BigBen

Reputation: 49998

To automatically add the datestamp:

Private Sub Worksheet_Change(ByVal Target As Range)      
    Dim rng as Range
    Set rng = Intersect(Target, Me.Range("D:D"))
    If rng Is Nothing Then Exit Sub

    On Error GoTo SafeExit
    Application.EnableEvents = False

    Dim cell as Range
    For Each cell in rng
         If Not IsEmpty(cell) Then ' don't do anything if cell was cleared
             cell.Offset(,1).Value = Date
         End If
    Next

SafeExit:
    Application.EnableEvents = True
End Sub

As far as the Correct/Wrong, this can easily be done with a formula (ISNUMBER(SEARCH(...)). I don't see the need for VBA here.

Even better, create a table using Ctrl+T. Excel will automatically add the formula in column A in new rows.

Upvotes: 0

Related Questions