1

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.

Improve this question
4
  • Do the Column B values allows follow a / in Column A? Commented Jul 11, 2018 at 7:05
  • I don't quite understand what you mean. I tried to say that the values in A can include more than just the B value. Like numbers and a slash before the value of the real value of B. I hope you understand, I don't know how to describe better -> the example above says it all I guess. Commented Jul 11, 2018 at 7:20
  • Is there any pattern to the how B can be contained within A? Commented Jul 11, 2018 at 7:23
  • 1
    Are B values always the last 9 characters in A values? Commented Jul 11, 2018 at 7:26

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

OP is searching B values in any row of A :-)
maybe if(isnumber(match("*"&b1&"*", a:a, 0)), "X", "")
@Jeeped Wow, thanks for the quick answers. So you mean I just put it in an excel cell like =if(isnumber(match("*"&b1&"*", a:a, 0)), "X", "") ?
Yes, the one above or Solar Mike's answer in the response above.
Do you mean: IF(IFERROR(MATCH(P2,B2:B1000,0),FALSE),"X","") (columns are adapted). Where do I write that code? When I write it in a cell it gives me an error, asking if I really wanted to put a function.
0

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?

Improve this answer

2 Comments

Thanks for your answer! The value in Column C, however can be anywhere in column B. E.g. 111/aaa can be in column B row 294. And when aaa is in row 211 in column C I have to find that (in B294) and mark the cell next to it with an "X".
I have added a picture in the question as further explanation.
0

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

Improve this answer

2 Comments

Thanks for your answer! However, the code doesn't work for me. It just puts an "X" in the first row column c. The thing is that any value of column B can be anywhere in column A. But some values of B are not in A. I hope you understood my question. Thank you for your thoughts.
ok i probably understood your question badly, i thought you search value from A in B so it puts "X" in collumn C next to founded B value , but if you can provide file it would be much easier to help you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.