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!
Why bother with vba?
All you need is this in column C:
Edit after comment: to look in all of column A - you can set the range:
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...
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
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?