Dynamic vlookup refering different workbooks through VBA

[rsy]

Hi All,

I having problem in applying vlookup through vba. there are two dynamic workbooks whose data will be keep on changing which is Test.xlsm (i had created some codes in it) and second workbook is SAMPLENAME.xlsx.
there were few modification required in Test.xlsm which i did with the vba code. however after inserting columns in text.xlsm file, i required to apply vlookup in column D (after running previous codes) and needs to populate values corresponding to range A3 to last row from file SAMPLENAME.xlsx column 2 i.e values present in "Zone" column.

Basically in both file "Dealer name" is common and I need to bring "Zone" against each "dealer name" from SAMPLENAME.xlsx into Test.xlsm file.

both file attached. and data in both file will be dyanamic so i'm looking for formula that may work in any data range condition.
I tried to do but getting so many errors. Please see if any one can help me out.

[SamT]

Moderator Bump

[offthelip]

Here you are, I have done it but NOT using Vlookup which is a very slow and inefficient way of coding. I never use it in VBA because using variant arrays is always faster and much more flexible.

Sub insert_ColumnAndVlookup()

'If False Then ' I put this in when testing to avoid adding more and more columns
Application.DisplayAlerts = False


ActiveSheet.Columns("c:c").Insert shift:=xlToRight, copyorigin:=xlformatfromrightorabove
ActiveSheet.Columns("c:c").Insert shift:=xlToRight, copyorigin:=xlformatfromrightorabove
Range("C1:C2").Select
    Selection.Merge
    Range("D1:D2").Select
    Selection.Merge
    Range("C1:C2").Select
    ActiveCell = "Region"
    Range("D1:D2").Select
    ActiveCell = "Zone"
    
Cells(Rows.Count, 2).End(xlUp).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
Selection.Value = "NorthEast"
Selection.End(xlUp).Select
'End If ' part of the if false above
MasterName = ActiveWorkbook.Name
'pick up data table from test workbook
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
table1 = Range(Cells(1, 1), Cells(lastrow, 1))
' initialise output array
Range(Cells(3, 4), Cells(lastrow, 4)) = ""
outarr = Range(Cells(1, 4), Cells(lastrow, 4))
'to openfile
' uncomment this line to open the workbook
'Workbooks.Open Filename:="B:\SAMPLENAME.xlsx"
' once you have done that you don't need this line
Windows("SAMPLENAME.xlsx").Activate
lastsample = Cells(Rows.Count, "A").End(xlUp).Row
table2 = Range(Cells(1, 1), Cells(lastsample, 2))




' loop through all of Test
For i = 3 To lastrow
 ' loop thorugh all of sample, for each row in test
 For j = 2 To lastsample
  If table1(i, 1) = table2(j, 1) Then
     'we have found the match, so write out to the output array
     outarr(i, 1) = table2(j, 2)
      ' stop the sample loop for this test row since we have found it
      Exit For
  End If
 Next j
Next i


Windows(MasterName).Activate
' write out the output
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr




End Sub

[rsy]

Hi Offthelip ,

A big thanks for your help and suggesting an alternate way to do this, I would have not think about it if you havn't suggested it. But to be frank, this code looks too complex for me , but no worry I will learn it and imbibe the its execution. Thanks again.