Do loop to add new item

[rjplante]

I have a large list of companies (1800+ rows) that I would like to search to see if the company added is in my list. If not, I want to add it. My code is listed below, but it takes quite a while to get through and then crashes on the Activecell.offset line in the do loop. What can I do to make this work.

    Sheets("Data").Range("C1").Select


        Do
            If ActiveCell.Value = Sheets("Data").Range("AA1").Value Then
                Exit Do
            ElseIf ActiveCell.Value <> Sheets("Data").Range("AA1").Value Then
                ActiveCell.Offset(1, 0).Select
            End If
        Loop

The code after this do loop will add the new company to the list. Please tell me why it crashes in the middle on the "ActiveCell.Offset(1, 0).Select" line. I get a Run-time error '1004' Application-defined or object-defined error.

Thanks for the help,

Robert

 

[My Aswer Is This]

So are you wanting to search Sheet("Data) column C for the value in:

Sheets("Data").Range("AA1")

And if not found add Range("AA1") value to the bottom of the column C range?

Is that what you want?
​

 

[My Aswer Is This]

Try this:

Sub My_Find()
'Modified  8/20/2018  6:04:02 PM  EDT
Application.ScreenUpdating = False
Dim SearchString As String
Dim SearchRange As Range
SearchString = Sheets("Data").Range("AA1").Value
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Set SearchRange = Range("C1:C" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then Cells(Lastrow + 1, "C").Value = SearchString
Application.ScreenUpdating = True
End Sub

 

[rjplante]

Yes. If the value in cell AA1 is found in column C then exit the sub. If it is not found, then add it to the bottom.

 

[My Aswer Is This]

Did my script work for you?

 

[rjplante]

Thanks, your code works awesome!!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks, your code works awesome!!!