CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

VBA with Vlookup function

I am having two sheets, Sht1 and sht2. With sht1, I always look into column B and check if the ID are matching with column B of sht2, if they are matching then , I copy the column Q value of sht1 to sht2.

I tried accomplishing through the below code, and I am not able to get the result, There is no error shown in the code.

I am lost what is wrong with my code. Could anyone help me to get rid of this.

Sub vlookupComments()
Dim totalrows As Long, totalrowsSht2  As Long
Dim ws As Worksheet
totalrows = Sheets("sht1").Cells(Rows.Count, "A").End(xlUp).Row
totalrowsSht2 = Sheets("sht2").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("sht1").Range("Q5:Q" & totalrows).Formula = Application.WorksheetFunction.IfError(Application.VLookup(Sheets("sht1").Range("B5:B" & totalrowsSht2), Sheets("sht2").Range("$A:$Q"), 17, 0), "")
End Sub

Upvotes: 0

Views: 150

Answers (2)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Your question says "copy column Q value of sht1 to sht2" but the formula does the other way. I go with the formula,

Try the below macro,

Sub vlookupComments()
Dim totalrows As Long, totalrowsSht2  As Long
Dim ws As Worksheet
totalrows = Sheets("sht1").Cells(Rows.Count, "A").End(xlUp).Row
totalrowsSht2 = Sheets("sht2").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To totalrows
    Sheets("sht1").Cells(i, "Q").Value = _
    Application.WorksheetFunction.VLookup(Sheets("sht1").Cells(i, "B"), _
    Sheets("sht2").Range("A1:Q" & totalrowsSht2), 17, 0)
Next i
End Sub

Option2:

Sub lookupmaturitystart()
Dim totalrows As Long, totalrowsSht2 As Long
totalrows = Sheets("Maturity_BU").Cells(Rows.Count, "A").End(xlUp).Row
totalrowsSht2 = Sheets("Maturity_Lastweek").Cells(Rows.Count, "A").End(xlUp).Row
For i = 5 To totalrows
For j = 1 To totalrowsSht2
If Sheets("Maturity_BU").Cells(i, "B") = Sheets("Maturity_Lastweek").Cells(j, "B") Then
    Sheets("Maturity_BU").Cells(i, "Q") = Sheets("Maturity_Lastweek").Cells(j, "Q")
End If
Next j
Next i
End Sub

Upvotes: 1

Srikanta Gouda
Srikanta Gouda

Reputation: 521

Try the below code 

Sheets("sht1").Range("Q5:Q" & totalrows).Value = "=IFERROR(VLOOKUP(B5,Sheet2!$A:$Q,17,0),"""")"

Upvotes: 0

Related Questions