Add find function or vlookup in macro

[johndenver120]

I want to change this code so if the values of sheet1 are in, for example "row36" instead of the corresponding row of sheet2, it will still place values in that row.

ex:
sheet1
row 1
row 2
row 3
...

sheet2
row 36
row 4
row 25
...etc...

Please Login or Register  to view this content.

[davesexcel]

Supplying a sample workbook may get you better results.

[Sa DQ]

Option Explicit
Sub FindAndAdd()
 Dim Sh As Worksheet, Rng As Range, sRng As Range, Cls As Range
 Dim MyAdd As String
 
 Sheets("S2").Select
 Set Sh = ThisWorkbook.Worksheets("S1")
 Set Rng = Sh.Range(Sh.[a1], Sh.[a1].End(xlDown))
 For Each Cls In Range([a1], [a1].End(xlDown))
    Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        MyAdd = sRng.Address
        Do
            If Cls.Offset(, 1).Value = sRng.Offset(, 2).Value Then
                Cls.Interior.ColorIndex = 35
                Sh.Cells(sRng.Row, "AI").Value = Cells(Cls.Row, "F").Value
                Sh.Cells(sRng.Row, "AE").Value = Cells(Cls.Row, "E").Value
                Sh.Cells(sRng.Row, "AA").Value = Cells(Cls.Row, "D").Value
                Sh.Cells(sRng.Row, "w").Value = Cells(Cls.Row, "C").Value
            End If
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
    End If
 Next Cls
 Sh.Select:                             Set Sh = Nothing
End Sub  

/-(ope this help.

[johndenver120]

Perfect!!! So happy! Thank you!!!