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]

Where do you intend to do the data entry? In a specific cell or use a button to get Inputbox?

 

[kdaniel86]

Where do you intend to do the data entry? In a specific cell or use a button to get Inputbox?

In the first empty cell of the column or for the second case, the column beside what i was searching for

 

[taurean]

I was referring to the preliminary input that user will do and not the actions which should happen after entering the data. Right click on the sheet tab and select option "View Code" and paste this code.

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
        rFind.Offset(0, 1).Value = "Already exists"
    End If
    Application.EnableEvents = True
End If
End Sub

After adding this code, start inputting the data in cell C1 only.

 

[My Aswer Is This]

Wow. This is a great little script. We have some very smart scripters here. A great way to make a shopping list. I added this piece of code to the last line of the if statement

Range("C1").select

 

[kdaniel86]

I was referring to the preliminary input that user will do and not the actions which should happen after entering the data. Right click on the sheet tab and select option "View Code" and paste this code.

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
        rFind.Offset(0, 1).Value = "Already exists"
    End If
    Application.EnableEvents = True
End If
End Sub

After adding this code, start inputting the data in cell C1 only.

Thank you so much! That works great

 

[taurean]

Thanks for the feedback kdaniel.

MyAswerIsThis,
Instead of

Range("C1").select

we can use:

Target.select

So place of changing cell address remains only one

 

[My Aswer Is This]

Yes that was a good plan. I was also going to add a script like this to the page but could not get it to work do you have a suggestion.

If Target.Address = "$C$3" Then
Range("B:B").ClearContents

 

[taurean]

What do you want to do?

Empty column B when something is entered in Cell C1? Or something else?

 

[My Aswer Is This]

I was just trying to see if I could add to the script. I clicked on cell C3 then it would cause column B:B to be cleared. It's not important I was just tweaking it. I use "if target" a lot but not an expert.