How can I add a specific text to the end of a column using VBA?

[kdaniel86]

For example,

An excel file with

A1
=======
Apple
Banana
Kiwi
Watermelon


I need the vba script to loop through the A1 column, and if a specific text does not exist, it will add the text to the very bottom of the column.

so for example, if i wanted to add peach, the result would be

A1
=======
Apple
Banana
Kiwi
Watermelon
Peach

since peach does not exist in A1. Also, if let's say I wanted to add "apple" but "apple" already exists, I would like to add text to the column beside it saying "already exists."

A1 B1
=======
Apple Already exists.
Banana
Kiwi
Watermelon



how can i do this using vba?

Any help would be appreciated.

Thanks.

 

[taurean]

I think I understand now. Please see if this is what you want to do:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFind As Range
If Target.Address = "$C$1" Then
    Application.EnableEvents = False
    Set rFind = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(Target.Value)
    If rFind Is Nothing Then
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Value
    Else
        Range("B:B").ClearContents
        rFind.Offset(0, 1).Value = "Already exists"
    End If
    Target.Select
    Application.EnableEvents = True
End If
End Sub

 

[My Aswer Is This]

Thanks taurean. That works great.