VlookUp

[Forestq]

Team,

In active file (name DATA_1, where I want to run a macro), in column "A" I have some data (numbers: 123, 160, 740, 100 etc).

Now I want open file (DATA_2 ) with source data.

In DATA_1, column "B" I want o use VlookUp, because I need to assigned value from DATA_2, coulmn 4. (should be something like this: 123 = Opel, 160 = Audi, 740 = BMW etc).

My code:

    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim strFile As String
 
 
    Set wksDest = Worksheets("Sheet1")
 
    MsgBox "Open file with source data"
    strFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
 
    If strFile = "False" Then Exit Sub
 
    Set wkbSource = Workbooks.Open(strFile)
    Set wksSource = wkbSource.Worksheets("Sheet1")
 
    With wksSource
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
        Set Rng = Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
    End With
 
    wksDest.Activate
    x_rows = Application.WorksheetFunction.CountA(Columns(1))
 
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[wksSource]Sheet1!Rng,4,0)"
   Selection.AutoFill Destination:=Range("x_rows"), Type:=xlFillDefault

I see for this moment one mistake - VB ask me twice to open file (DATA_2) . But I'm sure that my code need to be more modified.

Please help me with that.
regards
PvK

 

[CharlesChuckieCharles]

Not sure you are showing enough of the code?

BUT
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[wksSource]Sheet1!Rng,4,0)"

should Be

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & [wksSource] & "]!Rng,4,0)"

because when enclosed in quotes, "[wksSource]" is just "[wksSource]"
(NOT sure about the brackets in my version)


x_rows = Application.WorksheetFunction.CountA(Columns(1))
x_rows will contain something 1345 not a range
therefore

Selection.AutoFill Destination:=Range("x_rows"), Type:=xlFillDefault
won't work

Try
Selection.AutoFill Destination:=Range("B2:B" & x_rows), Type:=xlFillDefault

 

[Forestq]

this is all my code, with your changes:

    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim strFile As String
 
 
    Set wksDest = Worksheets("Sheet1")
 
    MsgBox "Open file with source data"
    strFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
 
    If strFile = "False" Then Exit Sub
 
    Set wkbSource = Workbooks.Open(strFile)
    Set wksSource = wkbSource.Worksheets("Sheet1")
 
    With wksSource
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
        Set Rng = Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
    End With
 
    wksDest.Activate
    x_rows = Application.WorksheetFunction.CountA(Columns(1))
 
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & [wksSource] & "]!Rng,4,0)"
    Selection.AutoFill Destination:=Range("B2:B" & x_rows), Type:=xlFillDefault

Now, I hava error with line:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & [wksSource] & "]!Rng,4,0)"

Run-time error '438'
Object doesn't support this property or method

 

[CharlesChuckieCharles]

try this

Sub bbs()
    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim strFile As String
[COLOR=green]    Dim Rng As String
    Dim sfullpath As String
[/COLOR]    
 
    Set wksDest = Worksheets("Sheet1")
 
    MsgBox "Open file with source data"
    strFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
 
    
    If strFile = "False" Then
        Exit Sub
    End If
    
    Set wkbSource = Workbooks.Open(strFile)
    Set wksSource = wkbSource.Worksheets("Sheet1")
 
    With wksSource
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
[COLOR=green]        Rng = Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Address
[/COLOR]    End With
[COLOR=green]    
    sfullpath = "[" & wksSource.Parent.Name & "]" & wksSource.Name
[/COLOR]    
    
    wksDest.Activate
    x_rows = Application.WorksheetFunction.CountA(Columns(1))
 
    Range("B1").Select
[COLOR=green]    ActiveCell.Formula = "=VLOOKUP(A1,'" & sfullpath & "'!" & Rng & ",4,0)"
[/COLOR]    
[COLOR=green]    Selection.AutoFill Destination:=Range("B1:B" & x_rows), Type:=xlFillDefault[/COLOR]
[COLOR=green]    [/COLOR]
[COLOR=green]    Application.DisplayAlerts = False
    wkbSource.Close
    Set wkbSource = Nothing
    Set wksSource = Nothing
    Application.DisplayAlerts = True
[/COLOR]End Sub

 

[Forestq]

works PERFECT!!!
THANKS A LOT for YOUR SUPPOR!!!

king regards
PvK