CODEWITHSUNDEEP
excelvba
Preston McInelly
Preston McInelly

Reputation: 11

Excel VBA Compare column A to Column B

I'm trying to run this code here:

Private Sub CommandButton1_Click()
    Dim Row_A As Integer, Row_B As Integer, Is_Copy As Boolean
    Is_Copy = False

    For Row_A = 1 To 850
        For Row_B = 1 To 10840
            Is_Copy = IsNumber(Search(Cells(Row_A, 1), Cells(Row_B, 2), 1))
            If Is_Copy Then Cells(Row_B, 2).Interior.Color = RGB(255, 0, 0)
            If Is_Copy Then Cells(Row_B, 3).Value = Is_Copy
            If Is_Copy Then Cells(Row_B, 4).Value = Row_A
            If Is_Copy Then Cells(Row_B, 5).Value = Row_B


            Is_Copy = False
        Next Row_B
    Next Row_A
End Sub

But i get a popup saying:

"Compile error: Sub or Function not defined"

It then highlights Line 1 and the Search in Line 7.

What do I need to do to fix this?

The issue I am trying to fix: I have two UID lists normally I would just compare the two of them, but Column b has 3 extra chars on the end so im using search to check to see if Column b Contains Column A.

Upvotes: 1

Views: 174

Answers (4)

Annette
Annette

Reputation: 234

as pointed above search is not a vb method or function. the best way to compare in a excel will be using the VLOOKUP excel function which dont need to write any vba script also.

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17647

Replace 

Is_Copy = IsNumber(Search(Cells(Row_A, 1), Cells(Row_B, 2), 1))

with something like 

Is_Copy = InStr(Cells(Row_A, 1), Cells(Row_b, 2)) > 0

IsNumber and Search are not VBA methods, they are worksheet functions.

Upvotes: 0

teylyn
teylyn

Reputation: 35990

Search is not a function in VBA. Unless you have a UDF with that name, the code will throw an error.

Upvotes: 1

Darrian Sheffield
Darrian Sheffield

Reputation: 31

I've been using this for that kind of function, found in a previous SO thread. It's not a VBS answer, but might be an easier way to accomplish your goal.

=IF(ISNA(VLOOKUP(<single column I value>,<entire column E range>,1,FALSE)),FALSE, TRUE)

-or-

=IF(ISNA(VLOOKUP(<single column I value>,<entire column E range>,1,FALSE)),"FALSE", "File found in row "   & MATCH(<single column I value>,<entire column E range>,0))

You could replace and with named ranged. That'd probably be the easiest.

Just drag that formula all the way down the length of your I column in whatever column you want.

Upvotes: 0

Related Questions