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