1

I have a situation where I have numbers in column A. I want to know if these numbers are found in column B. For example: A1 has the number 5554 and A2 has the number 163. B1 is 00051631 and B2 is 0000055549. The code should do the following: Search if one of the cells in column B contains 5554. Then move on to A2 and do the same.

Therefore both of these should return "true", as both numbers in column A exist in cells of column B. 

Sub Search() Dim StartCell As Integer Dim EndCell As Integer Dim i As Integer 'row counter Dim x As Integer 'row counter2 Dim InvoiceNumber As Integer StartCell = Worksheets("Sheet1").Range("A1") EndCell = Worksheets("Sheet1").Range("A1048576").End(xlUp).Row For i = 1 To EndCell InvoiceNumber = Cells(i, 1) If InStr(1, Cells(i, 2), InvoiceNumber) > 0 Then Cells(i, 3).Value = InvoiceNumber End If Next i End Sub

So basically what the above does is if A1 exists in B1, but it doesnt move on to check for B2 and so on...

Any solutions?

Thanks!

Improve this question
0

4 Answers 4

Reset to default
1

Assuming:

  • both your columns are actually numbers formatted as text (as per the leading zeros)
  • You need to return TRUE or FALSE as per your statement: "....Therefore both of these should return "true"

You could decide not to iterate over column B again and again. Instead you can perform a Find looking for values within values of column B:

Sample Code:

Sub Test() Dim lr1 As Long, lr2 As Long, x As Long Dim arr As Variant Dim rng As Range, cl As Range With Sheet1 'Change according to your sheets CodeName 'Fill the array for a loop in memory lr1 = .Cells(.Rows.Count, 1).End(xlUp).Row arr = .Range("A1:A" & lr1) 'Get the range to look in lr2 = .Cells(.Rows.Count, 2).End(xlUp).Row Set rng = .Range("B1:B" & lr2) 'Loop over the array and perform the search For x = LBound(arr) To UBound(arr) Set cl = rng.Find(arr(x, 1), LookIn:=xlValues) If Not cl Is Nothing Then .Cells(x, 3) = True 'If you want to insert boolean value '.Cells(x,3) = cl 'If you want to insert the found value '.Cells(x,3) = arr(x,1) 'If you want to insert the search value Else .Cells(x, 3) = False End If Next End With End Sub

Another way would be to work through memory without Find:

Sub Test() Dim lr1 As Long, lr2 As Long, x As Long Dim arr1 As Variant, arr2 As Variant, arr3 As Variant With Sheet1 'Change according to your sheets CodeName 'Fill the first array for a loop in memory lr1 = .Cells(.Rows.Count, 1).End(xlUp).Row arr1 = .Range("A1:A" & lr1) 'Fill the second array for a filter lr2 = .Cells(.Rows.Count, 2).End(xlUp).Row arr2 = Application.Transpose(Application.Index(.Range("B1:B" & lr2), 0, 1)) 'Loop over the array and perform the search For x = LBound(arr1) To UBound(arr1) 'Return an array of filtered values arr3 = Filter(arr2, arr1(x, 1)) 'Do something with the returned array If UBound(arr3) > -1 Then .Cells(x, 3) = True 'If you want to insert boolean value '.Cells(x,3) = arr3(1) 'If you want to insert the found value '.Cells(x, 3) = Join(arr3, ",") 'if you want to show all found values '.Cells(x,3) = arr(x,1) 'If you want to insert the search value Else .Cells(x, 3) = False End If Next End With End Sub

In both cases you might want to make sure you also format column C as text.

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

6 Comments

Hi, Thanks this is working great! The situation is simplified from the actual goal. I am assuming I can modify this so that basically A and B are just on different sheets and possibly making several "true" checks. Can you clarify that is the lr1 + arr basically defining the size of column A and lr2 + arr defining the column B?
Not sure what you mean with the +, but lr1 indeed does find the last used row of column A, which lr2 does for column B
Could you help me with modifying the code. What if the column A and B of the example are on different sheets?
@Carlsberg789, do you see where I open a With Sheets1 statement? All references after that untill the End With with a dot in front will refer to that parent worksheet. If you want to referer to another sheet you can open another with statement or directly refer to that worksheet: Worksheets("Sheets2").Range( etc...
Oh great thanks! Got it to work on separate sheets now. Looks ugly but does the job. > Is there a way to return the value of a cell next to the cell, where the value was found? For Example A1 has value 42 which was found in cell B45. I would like to return the value from C45.
|
0

You don't loop over B column at all, that's why you are having problem.

It is better to store row number in variable and loop between those variables:

Dim startRow As Long, endRow As Long For aRow = startRow To endRow invoiceNumber = Cells(aRow, 1).Value For bRow = startRow To endRow If InStr(1, Cells(bRow, 2).Value, invoiceNumber) > 0 Then Cells(aRow, 3).Value = invoiceNumber Exit For End If Next Next
Improve this answer

Comments

0

Here is my version

Sub Search() Dim StartCell As Integer Dim CellSource As Range Dim CellSearch As Range Dim SourceRange As Range Dim SearchRange As Range Dim testValue As String Dim i As Integer 'row counter Dim x As Integer 'row counter2 Dim InvoiceNumber As Integer StartCell = Worksheets("Sheet1").Range("A1") ' a you sure you need this? SourceRange = Range(Cells(Rows.Count, 1).Cells(Rows.Count, 1).End(xlUp)) SearchRange = Range(Cells(Rows.Count, 1).Cells(Rows.Count, 1).End(xlUp)) For Each CellSource In SourceRange For Each CellSearch In SearchRange testValue = "*" & CellSource & "*" If CellSearch Like testValue Then CellSource.Offset(0, 2).Value = CellSource End If Next Next i End Sub
Improve this answer

Comments

0

I know you are using VBA, but you know this could be done in a couple of ways using just an Excel formula:

=INDEX(B:B, MATCH("*" &A1&"*",B:B,0))
Improve this answer

6 Comments

Nice, according to OP's code, OP wants the Item in Column A to appear next to column B. Can you change it so column A is in column C
this will put it in the column where the formula is, so the formula just goes in column C
Yes i do in fact need it in VBA. But actually this will be extremely useful to me as well. Can you clarify the "" -parts of the formula. Basic match the situation is: (Value_to_search,Range_to_search_from,0). Is this basically: (Anything+Value_to_Search+Anything, Range_to_search_from,0))? What is the purpose of the space between the first "" and &A1&?
Actually i tried this, doesnt really work :) Is it possible to do an index match in a way that i have invoice 55534 and i want to match it with 115553400. Basically i have random numbers before and after, but the value is in the cell. So an isnumber + search would do the trick but this formula seemed easier
Aaand im continuing, works with text, not with numbers. Interesting.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.