problems with application.worksheetsfunction (vlookup)

**schzuki** · 01-16-2014, 09:31 AM

Hi

I'm running a code that looksup values in a different excel file and copies these back to my master file. I keep getting an error message with the following piece of code

Code:

ThisWorkbook.Worksheets(current).Cells(regel & rij).Value = Application.WorksheetFunction.VLookup(taak, Workbooks(Name).Worksheets("Results").Range(Cells(3, 2), Cells(29, 10)), 2, False)
ThisWorkbook.Worksheets(current).Cells(regel & (rij + 1)).Value = Application.WorksheetFunction.VLookup(taak, Workbooks(Name).Worksheets("Results").Range(Cells(3, 2), Cells(29, 10)), 6, False)
ThisWorkbook.Worksheets(current).Cells(regel & (rij + 2)).Value = Application.WorksheetFunction.VLookup(taak, Workbooks(Name).Worksheets("Results").Range(Cells(3, 2), Cells(29, 10)), 10, False)

ThisWorkbook.Worksheets(current).Cells(regel & (rij + 3)).Value = Application.WorksheetFunction.VLookup(taak, Workbooks(Name).Worksheets("Results").Range(Cells(3, 2), Cells(29, 10)), 20, False)
ThisWorkbook.Worksheets(current).Cells(regel & (rij + 4)).Value = Application.WorksheetFunction.VLookup(taak, Workbooks(Name).Worksheets("Results").Range(Cells(3, 2), Cells(29, 10)), 24, False)
ThisWorkbook.Worksheets(current).Cells(regel & (rij + 5)).Value = Application.WorksheetFunction.VLookup(taak, Workbooks(Name).Worksheets("Results").Range(Cells(3, 2), Cells(29, 10)), 28, False)

I keep getting an error 1004, (application-defined or object-defined error). I cant figure out what I'm doing wrong

Can you guys offer some advice?

**6StringJazzer** · 01-16-2014, 10:53 AM

Not enough code or context to provide a diagnosis. The code for VLOOKUP looks OK, but the error could be happening in several places.

Is the file Workbooks(Name) open at the point where it is used? If the file is not open, the code will fail. Although Excel formulas can refer to external files without the file being open, VBA code cannot use a range expression like

Workbooks(Name).Worksheets("Results").Range(Cells(3, 2), Cells(29, 10))

if the file is not open.

How are these variables declared? At the point where the error occurs, what are the values of these variables?

current
regel
rij
taak
Name

problems with application.worksheetsfunction (vlookup)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

problems with application.worksheetsfunction (vlookup)

Re: problems with application.worksheetsfunction (vlookup)

Thread Information

Users Browsing this Thread

Similar Threads

Having Problems with OnTime Application

Application Onkey Problems

[SOLVED] Problems with Application.Inputbox

[SOLVED] Problems using Application.IgnoreRemoteReferences

[SOLVED] Problems with Excel VBA application

Bookmarks

Bookmarks

Posting Permissions