using Lookup in VBA,Excel

Question

I have two Sheets , sheet1 and sheet2 .

Sheet1 has 27 columns, and sheet2 has 10 columns,

I am looking for the Id in sheet 1, column J and Need the corresponding date in sheet 2 , column g.

I Need this corresponding date to be printed in sheet 1 , column AA.

I am using the following belo VBA, it is printing the column D of sheet 2 insted of Column G.

This is the formula,

=IFERROR(VLOOKUP(j2;sheet2!$A:$L;7;0);"")

I dont want to use, record macro functionality. Kindly, help me to correct the code.

 Sub lookup()

Dim totalrows As Long
Dim totalcolumn As Long
Dim rng As Range
Dim rng1 As Range
Dim i As Long

totalrows = ActiveSheet.UsedRange.Rows.Count

For i = 2 To totalrows

Sheets("Sheet1").Select

Set rng = Sheets("Sheet2").UsedRange.Find(Cells(i, 10).Value)

If Not rng Is Nothing Then

Cells(i, 27).Value = rng.Value

End If

Next i

End Sub

why rng.Offset? i Need the date from column g. If i Need some other data, i think, i could used Offset. — Mikz
@Nathan_Sav I am still not sure, if it is pasting the corresponding data from column D. In this case, I guess i am missing some Key Point in the code. — Mikz
Try limiting your find to the column Sheets("Sheet2").columns("G").find you are looking in, then use offset, if needed, it can exist anywhere in the sheet this way. — Nathan_Sav
how do i do it ? Sorry, for the direct question, i am new to vba — Mikz

Mrig Mrig · Accepted Answer · 2017-06-26T10:46:12

Instead of FOR loop you can use:

Sheets("Sheet1").Range("AA2:AA" & totalrows).Formula = Application.WorksheetFunction.IfError(Application.VLookup(Sheets("Sheet1").Range("J2:J15"), Sheets("Sheet2").Range("$A:$L"), 7, 0), "")

EDIT:_____________________________________________________________________________

Sub lookup()
    Dim totalrows As Long, totalrowsSht2 As Long
    totalrows = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    totalrowsSht2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Sheet1").Range("AA2:AA" & totalrows).Formula = Application.WorksheetFunction.IfError(Application.VLookup(Sheets("Sheet1").Range("J2:J" & totalrowsSht2), Sheets("Sheet2").Range("$A:$L"), 7, 0), "")
End Sub

using Lookup in VBA,Excel

1 Answers