VBA Application Vlookup

**Elland** · 12-14-2018, 09:05 AM

Good Morning,

Please can someone offer some advice.

I have the following line of VBA code

Range("F2:J2") = Application.Evaluate("VLOOKUP(E2, 'Sheet2'!$A$1:$K$1000,{2,3,4,8,7},FALSE)")

This code does what I require; getting values from Sheet 2 when a particular code is input to cell E2 on sheet 1 (and making text rather than formula in the cells in sheet 1). So when code in input in E2, then automatically values on F2, G2, H2, I2, J2 are populated with information from sheet 2.

But i'm not sure how to 'copy' this formula down to say "F3:J3", "F4:J4", etc all the way down to perhaps "F200:J200"

Can anyone help me. I hope my post makes sense to you.

Thanks for considering this post.

**Bernie Deitrick** · 12-14-2018, 11:24 AM

Try code like

Sub TestMacro()
    Dim lngR As Long
    For lngR = 2 To 200
        With Cells(lngR, "F").Resize(1, 5)
            .FormulaArray = "=VLOOKUP(E" & lngR & ", 'Sheet2'!$A$1:$K$1000,{2,3,4,8,7},FALSE)"
            .Value = .Value
        End With
    Next lngR
End Sub

**Elland** · 12-16-2018, 05:58 AM

Thank you. This works so thank you for you help (sorry I could not respond sooner to you). Good day.

VBA Application Vlookup

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Application Vlookup

Re: VBA Application Vlookup

Re: VBA Application Vlookup

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] application vlookup error runtime error 1004, unable to get the vlookup property of the.."

[SOLVED] how to use application.worksheetfunction.vlookup

[SOLVED] Application.vlookup not working

Vlookup with application.worksheetfunction in vba

Application.WorksheetFunction.VLookup

application.vlookup

[SOLVED] Application.Calculate & VLOOKUP

Bookmarks

Bookmarks

Posting Permissions