VBA Help - Reference to a Table & Vlookup

[mapaulie]

Hi,

I'm a Newbie to Excel VBA.

I have 2 .xlsx files named, Template.xlsx and RequestChange.xlsx (the data in this file is in a Table view). Trying to Vlookup data and have it returned in the Template file, while RequestChange.xlsx is a closed book.

I wrote the following code but keeps getting stuck on referencing to the Table. Also, end goal is to Vlookup the whole Table and return data in specific columns, like column B, C, E, F (for example).

Please help! Tia

---------------------------------------------------------------------------------------------

Sub Vlookup()
Dim rw As Long, x As Long, x As Range
    Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
    Set extwbk = Workbooks.Open("C:\Users\xxxx\MyDocument\RequestChange.xlsx", True, True)
    Set x = extwbk.Worksheets("Sheet1").ListObjects("Change__2").Range.Select
With twb.Sheets("Form")
For rw = 2 To .Cells(Rows.Count, 1).End(x1Up).Row
            On Error Resume Next
            .Cells(rw, 2) = Application.Vlookup(.Cells(rw, 1).Value2, x, 2, False)
        Next rw
End With
extwbk.Close savechanges:=False
End Sub

[mancubus]

welcome to the forum.

do not use vba specific or reserved words (such as vlookup, path, etc) as procedure or variable names.
do not assign the same word to different variables (x as long, x as range in your code).
use CODE tags when posting your code (see #1 in my forum signature).

that being said, you may try this:

Sub vbax_68443_Vlookup()

    Dim rw As Long
    Dim twb As Workbook
    Dim arr
    
    Set twb = ThisWorkbook
    Workbooks.Open ("C:\Users\xxxx\MyDocument\RequestChange.xlsx")
    arr = Worksheets("Sheet1").ListObjects("Change__2").DataBodyRange.Value
    extwbk.Close savechanges:=False
    
    On Error Resume Next
    
    With twb.Sheets("Form")
        For rw = 2 To .Cells(.Rows.Count, 1).End(x1Up).Row
            .Cells(rw, 2) = Application.Vlookup(.Cells(rw, 1).Value2, arr, 2, False)
        Next rw
    End With

End Sub

[p45cal]

try:

Sub myVlookup()
With Workbooks.Open("C:\Users\xxxx\MyDocument\RequestChange.xlsx", True, True)
  With ThisWorkbook.Sheets("Form")
    With .Range("B2:B" & .Cells(.Rows.Count, 1).End(xlUp).Row)
      .FormulaR1C1 = "=VLOOKUP(RC[-1],RequestChange.xlsx!Change__2[#Data],2,FALSE)"
      .Value = .Value
    End With
  End With
  .Close savechanges:=False
End With
End Sub

(Untested)

[snb]

Avoid Excel functions (like vlookup) in VBA: VBA is faster.
Minimize interactions with the workbook (reading/writing)

Sub M_snb()
  sn = thisworkbook.sheets("form").cells(1).currentregion.resize(,2)
  with Getobject("C:\Users\xxxx\MyDocument\RequestChange.xlsx")
    sp = .sheets(1)).ListObjects(1).DataBodyRange
    .Close 0
  end with

  with createobject("scripting.dictionary")    
    for j = 1 to ubound(sp)
      .item(sp(j,1)) = sp(j,2)
    next

    for j = 2 to ubound(sn)
      if .exists(sn(j,1)) then sn(j,2) = .item(sn(j,1))
    next
  End With

  thisworkbook.sheets("form").cells(1).currentregion.resize(,2) = sn
End Sub