VBA Vlookup Code Error for Looping

[dpiano1984]

Hello all,

I've been working on a code for a project I'm working on that will perform a vlookup from one workbook to the next and loop it multiple times until the end of the worksheet I'm working on. I keep getting a a few errors. It almost never works properly so I can't implement the code into my normal daily activity yet. Here is the code that I have written so far:

Dim myfile as string
msgbox("Please select a file you would like to reference")
myfile = application.getopenfilename

Dim index as integer
index = 3
Dim iCell as integer
iCell = 4
Dim aCell as integer
aCell = 2

Do
Do while iCell < 16
Cells(aCell,iCell).formula = "=vlookup(a2,'[myfile]'!C1:O2000,index,false)"
iCell = iCell + 1
index = index + 1
Loop
aCell = aCell + 1
Loop

That's what I have so far. I did notice that I will have to find a way to change Cell A2 each time, as I want vlookup to just reference the column A. Any help would be greatly appreciated.

Cheers

 

[Andrew Poulsom]

You need to open the file. GetOpenFileName only returns the name; it doesn't open it.

 

[dpiano1984]

I wasn't looking to open that file, just use it as the source. That and it raised other problems in getting back to the original workbook. I couldn't find anything on switching between workbooks.

 

[Andrew Poulsom]

What errors do you get and on what lines of code?

 

[dpiano1984]

The only line I'm receiving an error for is the vlookup. It keeps giving me a range error.

I've changed the code around to open the workbook that I'm referencing and then trying a different code. But getting a new error:

Dim myfile As String
MsgBox ("Please Select the file you would like to reference.")
myfile = Application.GetOpenFilename








Dim MWB As Workbook
Set MWB = ActiveWorkbook
MWB.Activate
Workbooks.Open Filename:=myfile
MWB.Activate






Dim index As Integer
index = 3
Dim iCell As Integer
Dim aCell As Integer
iCell = 4
aCell = 2


Do While aCell < 202


    Do While iCell < 16
        Cells(aCell, iCell).Select
        ActiveCell.Application.WorksheetFunction.VLookup(Cells(aCell, 1), Workbooks(myfile).Range("C:O"), index, False) = result
        
        
        
        iCell = iCell + 1
        index = index + 1
    Loop
    aCell = aCell + 1
Loop

I'm now getting an error in the same line as the vLookup, it doesn't like the table array.

 

[Andrew Poulsom]

Your syntax is wrong and you can't VLookup an external workbook using WorksheetFunction in VBA. Going back to your original post the syntax for VLOOKUP when the source workbook is open is:

=VLOOKUP(A2,'[Book 1.xls]Sheet1'!C1:O2000,2,FALSE)

but when it's closed it's:

=VLOOKUP(A2,'C:\Users\Andrew\Documents\Test\[Book 1.xls]Sheet1'!C1:O2000,2,FALSE)

You were using the former but with the full path in the square brackets, except that, in the absence of concatenation, myfile would be taken literally rather than being what's assigned to the variable. So either way there's some non-trivial parsing to be done.

Why don't you just put the formulas on the workheet and use Edit|Links if you want to change the source?

 

[dpiano1984]

I see what you're saying now. And that makes sense.

I need the formula to be different in 13 columns and 201 rows. I normally just type in the vlookup in the first row, and then bring them all down, but this gets tedious to do many times in a day (when the actual work is needed to be done). So I thought a macro would speed up the process of getting the information needed to work.

I wrote the code to have the workbook open and I'm still getting a range error with the vlookup. I'm wondering if the best way to go about automating this task is to maybe have the macro do a search function on the referencing workbook?

 

[Andrew Poulsom]

In what way do you need the formula to be different? If it's just the column_index, that can be dealt with quite easily,eg:

=VLOOKUP($A2,'[Book 1.xls]Sheet1'!$C$1:$O$2000,COLUMNS($A2:B2),FALSE)

It will increment from 2 as the formula is copied across.