Excel VBA compare value to column

I have the following problem: I have an excel with 2 columns A and B. In A there are different serial numbers which can contain values of the different B values. Between the rows there can be empty cells in both columns.

What needs to be done by a VBA script: The script has to take the values of B and search that in the column A. Remember: the values can be: A567=2174/ENJFA7384 and B45=ENJFA7384 -> that would mean they match. If they match there should be written a "X" in the column C. If there is no match for the value of B in A it would just continue.

I have tried to achieve that and it works but the program crashes because it is very inefficient. Here the code (that script marks the cell yellow instead of writing the "X"):

Sub suchen()
'in Spalte A nach einem Namen suchen
'die Zeilen farblich markieren und
'die Anzahl der Treffer anzeigen

Dim suche As String
Dim z As Integer

Dim a As Range, b As Range

Set a = Range("P2:P391")

For Each b In a.Rows

suche = b.Value

'hier ändern falls eine andere Spalte durchsucht werden soll
[B10].Activate

'wenn keine Eingabe in InpuBox erfolgte wird abgebrochen
If suche = "" Then Exit Sub

'bis zur ersten leeren Zelle suchen
Do Until ActiveCell = "STOP"
'eine Zeile nach unten gehen
ActiveCell.Offset(1, 0).Activate
   'wenn die Zelle den gesuchten Wert enthält:
    If ActiveCell Like suche Or ActiveCell Like "*" & suche Or ActiveCell = suche Then

   'und die Zelle gelb markieren
    ActiveCell.Interior.ColorIndex = 36
    End If
Loop
Next

End Sub

If there are any questions feel free to ask. Any ideas on how to make this work? Any new ideas are highly appreciated. Thank you in advande!

EDIT Here is an example picture of what the script should do: The yellow marks don't have to be made.

3 answers

  • answered 2018-07-11 05:53 Solar Mike

    Why bother with vba?

    All you need is this in column C:

    =if(A1=B1,"X","")
    

    Edit after comment: to look in all of column A - you can set the range:

    =IF(IFERROR(MATCH(B1,A1:A10,0),FALSE),"X","")
    

    Just enter in cell C1 or whichever cell you want to start, make sure that "B1 or B16" is the first cell you want to check and drag down...

    Edit 2 see image and note the "No Val" update... enter image description here

  • answered 2018-07-11 06:22 Exelus

    I think you are searching for something like this :

    Sub test()
    
    
    For i = 1 To Cells(Rows.Count, 10).End(xlUp).Row 'just change "1" to whenever     column you want to count rows from
    For b = 1 To Cells(Rows.Count, 12).End(xlUp).Row
    
    Dim search As Variant, Find As Variant
    
    search = Cells(b, 12).Value
    Find = Cells(i, 10).Value
    
    If Find Like "*" & search & "*" Or Find = search Then
    
    Cells(b, 2).Offset(0, 1).Value = "x"
    
    End If
    
    If Cells(i, 1).Value = "" Then
    Exit Sub
    End If
    
    Next b
    Next i
    
    End Sub
    

    it worked for me, so i hope it will help you too.

    *edited to find B value in A column and put "X" next to Searched B value

  • answered 2018-07-11 07:53 Dominique

    Why do you even use VBA for this?

    I tried using the following situation:

          | Column A | Column B | Column C | Column D
    Row 2 |          | 111/aaa  |      aaa | =IFERROR(FIND(C2;B2);0)
    Row 3 |          | 222/bbb  |          | =IFERROR(FIND(C2;B3);0)
    Row 4 |          |          |          | =IF(D2+D3=0;"X";D2+D3)
    

    Is this what you want?