Finding values between sheets

[Grazier]

Hello guys!

This is my first post here, and I'm looking for help to the following issue:

I have an excel table with 2 sheets, (Sheet1, Sheet2)

Starting from cell A2, I have the values that needs to be found in next sheet (Sheet2)

for example:
Column A
A1: Title
A2: A00048
A3: A00098
A4: A00108
A5: A00109
etc.
(Goes on to row 1500)

In the sheet 2, I have values in columns A and B

So the macro would have to search the values of sheet1 from sheet2.

Once it finds, the value; for example A00048 (these are in Column B of Sheet2), from a string like:

A00048, D00121, D00136, D00195, D00201, D00209, D00213, D00287

It goes 1 cell to the left (to column A) and copies its value; for example "5023".

Then it goes back to Sheet1, where it needs to look the same value (A00048) again and paste the copied value "5023" eleven steps to the right from the found cell.

This is the step where I got stuck; the problem is, how can I search all the cells from Sheet1 column A in order since the find option "Ctrl+F" has a limit of 1 search at a time?

Also, all of the values in Sheet1 can not be found from Sheet2, so it would have to skip the ones that aren't found and move onto the next one.

Here is what I have at the moment:

Sub Macro1()

Sheets("Sheet2").Select

Cells.Find(What:="A00048", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Selection.Offset(0, -1).Select

Selection.Copy
Sheets("Sheet1").Select

Cells.Find(What:="A00048", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Selection.Offset(0, 11).Select
ActiveSheet.Paste

Selection.Offset(0, -11).Select
'I added this row so that it would go back to the column A (let's say A2)
'Now it should start over, but searching the value of A3

End Sub

as you can see, it only looks for 1 value that is pretermined.

Im not even sure if it's possible to do this, so I wanted ask you guys.


All the help would be appreciated and I can give you guys any additional information if it's needed.

Thank you for your answers!

And sorry for such a long post, just trying to give you guys all the information you might need


-Grazier

 

[mumps]

Hello Grazier. Will this code do what you want?

Sub Test()
    Dim bottomA As Integer
    bottomA = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim a As Range
    Dim bottomB As Integer
    bottomB = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
    Dim b As Range
    For Each a In Sheets("Sheet1").Range("A2:A" & bottomA)
        For Each b In Sheets("Sheet2").Range("B2:B" & bottomB)
            If b = a Then
                b.Offset(0, -1).Copy Sheets("Sheet1").Range("A" & a.Row).Offset(0, 11)
            End If
        Next b
    Next a
End Sub

 

[Grazier]

Hello mumps,

Thank you for your answer, unfortunately it doesn't.

The problem with the code above is that, it looks for exact matches. The values to be searched (Sheet2, column B) are in a text string.

Like: A00048, D00121, D00136, D00195, D00201, D00209, D00213, D00287

If the value in sheet 2 column B would only be: A00048

Then your code would find it. Awesome code by the way

Can it be modified so that it would look for the values that are contained in a text string?

Thank you very much!

 

[mumps]

Hi Grazier. I'm not exactly sure what you mean by a text string. The examples you listed in your last post are exact matches for those in Sheet1. Do you mean that the range in Sheet2 contains data such as "A00048hello" and you want to find only "A00048"? Could you please clarify for me.

 

[JLGWhiz]

See if this will work for you.

Code:
Sub findNpaste()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, r As Range
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
For Each c In rng
If c <> "" Then
Set r = sh2.Range("B:B").Find(c.Value, LookIn:=xlValues, LookAt:=xlPart)
If Not r Is Nothing Then
r.Offset(0, -1).Copy c.Offset(0, 11)
End If
End If
Next
End Sub
Code:

 

[Grazier]

Hey,

Yes, the sheet 2 column B contains such values as "something A00048 something A00090" etc.

So it needs to find values that are contained in such data. Unfortunately I can't test the new code you provided until Monday next week. But I will get back to you and let you know if it works.

Thanks a lot for your assistance! Your help has been invaluable.

-Grazier

 

[mumps]

Hi Grazier. The following code should work for you if the value you search for always has 6 characters (e.g. A00048) and the value is always at the end of the value searched. See if this will work for you.

Sub Test()
    Dim bottomA As Integer
    bottomA = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim a As Range
    Dim bottomB As Integer
    bottomB = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
    Dim b As Range
    For Each a In Sheets("Sheet1").Range("A2:A" & bottomA)
        For Each b In Sheets("Sheet2").Range("B2:B" & bottomB)
            If a = Right(b.Value, 6) Then
                b.Offset(0, -1).Copy Sheets("Sheet1").Range("A" & a.Row).Offset(0, 7)
            End If
        Next b
    Next a
End Sub

 

[Grazier]

Hello guys!

I have tried the new codes, and must say they are both great! I have many uses for both of them

For what I was looking for, JLGWhiz's code worked exactly how I wanted it to! As for mumps's code, there are values to be searched also in the middle of the string so it wasn't exactly what I was looking for, but I actually had a case in which I implemented your piece of code

Thanks alot to both of you! This case has been solved.

 

[JLGWhiz]

Hello guys!

I have tried the new codes, and must say they are both great! I have many uses for both of them

For what I was looking for, JLGWhiz's code worked exactly how I wanted it to! As for mumps's code, there are values to be searched also in the middle of the string so it wasn't exactly what I was looking for, but I actually had a case in which I implemented your piece of code

Thanks alot to both of you! This case has been solved.

Thanks for the feedback.
Regards, JLG