Lookup macro help needed

[John T]

Hi,

I have data in column G of a spreadsheet (Sheet1).
I then have data in another sheet (Data) in column A and a value for the data in column B.

I need a macro to look at data in each row of column A for the second sheet and if it appears in column G of the first sheet to return the value from column B of the second sheet in column AM of the first sheet.

If possible i then need to highlight the cells that were successfully found in sheet 2 yellow so i can identify any that were not contained within the first sheet.

Thanks

 

[mumps]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Data").Range("A2:A" & LastRow)
        Set foundRng = Sheets("Sheet1").Range("G:G").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(foundRng.Row, "AM") = rng.Offset(0, 1)
            foundRng.Interior.ColorIndex = 3
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[John T]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Data").Range("A2:A" & LastRow)
        Set foundRng = Sheets("Sheet1").Range("G:G").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(foundRng.Row, "AM") = rng.Offset(0, 1)
            foundRng.Interior.ColorIndex = 3
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

That works great. Just one or two queries. It misses the first row of data in cell A1.
Do i just need to change this line from A2 to A1?

For Each rng In Sheets("Data").Range("A2:A" & LastRow)

Secondly is it possible to also highlight the data in column A of the data sheet when it is found.
I like that the data in sheet1 is highlighted when found but i will need to identify what data in column A of the data tab is missing from sheet1.

Thanks for your help.

 

[John T]

Also is it possible to enter a date in column c of the data tab and add this to cell AL of sheet 1 once it finds the matching cell in the original macro.

 

[mumps]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Data").Range("A1:A" & LastRow)
        Set foundRng = Sheets("Sheet1").Range("G:G").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(foundRng.Row, "AM") = rng.Offset(0, 1)
            foundRng.Interior.ColorIndex = 3
            rng.Interior.ColorIndex = 3
            Sheets("Data").Range("C" & rng.Row) = Date
            Sheets("Sheet1").Cells(foundRng.Row, "AL") = Date
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[John T]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Data").Range("A1:A" & LastRow)
        Set foundRng = Sheets("Sheet1").Range("G:G").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(foundRng.Row, "AM") = rng.Offset(0, 1)
            foundRng.Interior.ColorIndex = 3
            rng.Interior.ColorIndex = 3
            Sheets("Data").Range("C" & rng.Row) = Date
            Sheets("Sheet1").Cells(foundRng.Row, "AL") = Date
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

That works great, Thanks. The only issue i'm having is that the date i have in column C of the data work sheet is changing to today's date on both the data sheet and sheet1 when the macro is run. I need it to copy the date that is in the relevant row of column c and transfer that to sheet1. Is that possible?

 

[mumps]

Does this work for you?

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Data").Range("A1:A" & LastRow)
        Set foundRng = Sheets("Sheet1").Range("G:G").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(foundRng.Row, "AM") = rng.Offset(0, 1)
            foundRng.Interior.ColorIndex = 3
            rng.Interior.ColorIndex = 3
            Sheets("Sheet1").Cells(foundRng.Row, "AL") = Sheets("Data").Range("C" & rng.Row)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[John T]

Does this work for you?

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Data").Range("A1:A" & LastRow)
        Set foundRng = Sheets("Sheet1").Range("G:G").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(foundRng.Row, "AM") = rng.Offset(0, 1)
            foundRng.Interior.ColorIndex = 3
            rng.Interior.ColorIndex = 3
            Sheets("Sheet1").Cells(foundRng.Row, "AL") = Sheets("Data").Range("C" & rng.Row)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Thanks, that looks like its working just as i want it. Thank you for your help.

 

[mumps]

You are very welcome.