CODEWITHSUNDEEP
vbaexcel
Johannes
Johannes

Reputation: 23

VBA vlookup with defined range and file from other workbook

I'm working in the workbook "File". Then, I open another workbook (Masterfile) which is used to look up data.

Workbooks.Open FileName:=Path & Masterfile

lRowMasterfile = Cells(Rows.Count, "A").End(xlUp).Row
SelectionMasterfile = Range("A1").CurrentRegion.Address

Workbooks(File).Activate

Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & Masterfile"' & ! & SelectionMasterfile,1, FALSE)"

Range("K2").AutoFill Destination:=Range("K2:K" & lRowFile)

Workbooks(Masterfile).Close

I define Masterfile and Range to use it in other documents but unfortunately it does not work. Can anybody help?

Upvotes: 2

Views: 1889

Answers (3)

ChipsLetten
ChipsLetten

Reputation: 2953

You've got two issues.

This line gives you an address in A1 notation (e.g. $A$1:$B$3):

SelectionMasterfile = Range("A1").CurrentRegion.Address

But you are building the formula using R1C1 notation (e.g. R1C1:R3C2) and you are missing the worksheet name. Try this:

SelectionMasterfile = ActiveSheet.Name & "!" &  Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

The other problem is there's an error in where you've got speech marks.

Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & Masterfile & "]'" &  SelectionMasterfile & ",1, FALSE)"

PS You should always try to fully qualify sheets and ranges. Look up guides to using Workbook, Worksheet and Range object variables.

Upvotes: 1

user4039065
user4039065

Reputation:

You are dealing with workbooks like they are the worksheets within the workbooks. Specifying the external workbook is important but so is the worksheet within that external workbook that you are retrieving information from.

Thankfully, the Range.Address property can take care of a lot of this by specifying the optional external parameter.

Dim mwb As Workbook
Dim lRowMasterfile As Long, lRowFile As Long, SelectionMasterfile As String
Dim Path As String, Masterfile As String, File As String

Path = ThisWorkbook.Path            '<~~ set this properly!
Masterfile = "MasterWorkbook.xlsx"  '<~~ set this properly!
File = ActiveWorkbook.Name  '<~~ set this properly!

Set mwb = Workbooks.Open(Filename:=Path & Masterfile)

With mwb
    With .Worksheets("Sheet1")    '<~~ what worksheet are you trying to get information from?!?
        lRowMasterfile = .Cells(Rows.Count, "A").End(xlUp).Row
        SelectionMasterfile = .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1, external:=True)
    End With
End With

With Workbooks(File)
    With .Worksheets("Sheet1")    '<~~ what worksheet are you trying to put information into?!?

        lRowFile = 0   '<~~ set this properly!

        'this VLOOKUP only checks to see if the value exists, it doesn't lookup anything but the first column
        'in any event, you can put them all in at the saame time
        .Range("K2:K" & lRowFile).FormulaR1C1 = _
            "=VLOOKUP(RC[-1], " & SelectionMasterfile & ", 1, FALSE)"

    End With
End With

mwb.Close False
Set mwb = Nothing

There was a lot of missing information but if you get all the vars assigned correctly, this should be a good framework to get started.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152525

not sure if there are typos in what you posted or if you pasted a copy directly from your code bu there are errors It should look like this:

Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & Masterfile.Name & "'!" & SelectionMasterfile & ",1, FALSE)"

Upvotes: 0

Related Questions