VBA - Trouble with VLookup

[Christoffer]

Hello

I'm having some trouble getting the VLookup function to work cross worksheets.

Current code:

Sub GetData()
Dim sum As Single
Dim hour1 As Single
Dim hour2 As Single
Dim hour3 As Single
Dim hour4 As Single
Dim hour5 As Single
Dim q As Integer
Dim PasteWks As Worksheet


    Set myWb = ActiveWorkbook
    Set DstWks = myWb.Sheets("Table")
    Set DstRng = DstWks.Range("G2")
    Set PasteWks = myWb.Sheets("PasteSheet")
    q = 1
    DstRng.Offset(q, 0) = k
    
    
    FilePath = Application.GetOpenFilename("All Files (*.*),*.*")
    Set xlw = xlo.Workbooks.Open(FilePath)
    PasteWks.Range("B1:N2500").Value = xlo.Worksheets(3).Range("A1:M2500").Value
    
    For Each c In Sheet1.Range("B:B")
        hour1 = Application.WorksheetFunction.VLookup(c, Sheet3.Range("B1:N2500"), 8, False)
        hour2 = Application.WorksheetFunction.VLookup(c, Sheet3.Range("B1:N2500"), 9, False)
        hour3 = Application.WorksheetFunction.VLookup(c, Sheet3.Range("B1:N2500"), 10, False)
        hour4 = Application.WorksheetFunction.VLookup(c, Sheet3.Range("B1:N2500"), 11, False)
        hour5 = Application.WorksheetFunction.VLookup(c, Sheet3.Range("B1:N2500"), 12, False)
        sum = hour1 + hour2 + hour3 + hour4 + hour5
        DstRng.Offset(q, 0) = sum
        q = q + 1
    Next c
    
    xlw.Close
    Set xlo = Nothing
    Set xlw = Nothing
End Sub

This does not work.The only way I can get the VLookup function to work is by using just one sheet(sheet1) and the "For Each" range starts on the same column as the lookup range.
The function works here, but I am not able to go through the desired data since it's stored in another sheet(sheet3)

Sub GetData()
Dim sum As Single
Dim hour1 As Single
Dim hour2 As Single
Dim hour3 As Single
Dim hour4 As Single
Dim hour5 As Single
Dim q As Integer
Dim PasteWks As Worksheet






    Set myWb = ActiveWorkbook
    Set DstWks = myWb.Sheets("Table")
    Set DstRng = DstWks.Range("G2")
    Set PasteWks = myWb.Sheets("PasteSheet")
    q = 1
    DstRng.Offset(q, 0) = k
    
    
    FilePath = Application.GetOpenFilename("All Files (*.*),*.*")
    Set xlw = xlo.Workbooks.Open(FilePath)
    PasteWks.Range("B1:N2500").Value = xlo.Worksheets(3).Range("A1:M2500").Value
    
    For Each c In Sheet1.Range("B:B")
        hour1 = Application.WorksheetFunction.VLookup(c, Sheet1.Range("B1:N2500"), 8, False)
        hour2 = Application.WorksheetFunction.VLookup(c, Sheet1.Range("B1:N2500"), 9, False)
        hour3 = Application.WorksheetFunction.VLookup(c, Sheet1.Range("B1:N2500"), 10, False)
        hour4 = Application.WorksheetFunction.VLookup(c, Sheet1.Range("B1:N2500"), 11, False)
        hour5 = Application.WorksheetFunction.VLookup(c, Sheet1.Range("B1:N2500"), 12, False)
        sum = hour1 + hour2 + hour3 + hour4 + hour5
        DstRng.Offset(q, 0) = sum
        q = q + 1
    Next c
    
    xlw.Close
    Set xlo = Nothing
    Set xlw = Nothing
End Sub

Is there some way to make the VLookup function work across sheets and with one range in the For each loop and a different range in the lookup function?

 

[AlphaFrog]

In VBA, you can only use a sheet's "code name" e.g. Sheet1 to reference the sheets in ThisWorkbook (the workbook with the macro code). Code names cannot reference sheets in an external workbook (the workbook you open).

So change the worksheet code names from Sheet1 to something like Sheets(1) or Sheets("Sheet1")

 

[Christoffer]

If you look at my code you can see the line before the For-loop, this copies the data I need from the other workbook to the one I use, and saves it in sheet3.
So the VLookup funtion will have to match data in only one workbook, but two different sheets.

 

[Christoffer]

Update:

The issue seems to be with the For-loop.

For Each c In Sheet1.Range("B:B")
        hour1 = Application.WorksheetFunction.VLookup(c, ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500"), 8, False)
        hour2 = Application.WorksheetFunction.VLookup(c, ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500"), 9, False)
        hour3 = Application.WorksheetFunction.VLookup(c, ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500"), 10, False)
        hour4 = Application.WorksheetFunction.VLookup(c, ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500"), 11, False)
        hour5 = Application.WorksheetFunction.VLookup(c, ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500"), 12, False)
        sum = hour1 + hour2 + hour3 + hour4 + hour5
        DstRng.Offset(q, 0) = sum
        q = q + 1
    Next c

It loops through each cell in the range B:B and since the cell B1 is a header with a string the VLookup function errors out when trying to find it.
Solution for now: Use Sheet1.Range("B2:B2500") as the range.

 

[AlphaFrog]

EDIT: this was posted before seeing your "update" post

You're right. Sorry I miss that part.

The Vlookup syntax in your code looks fine to me. So I'm not sure why it doesn't work.
Do you have a header row in row 1?

One thing I did notice; I don't think you want to loop through every cell in the entire column B
For Each c In Sheet1.Range("B:B")


Here's another version of the code. Maybe it will help.
It loops through the used cells in Sheet1 column B.
It also tests if a match was found.

[COLOR=darkblue]Sub[/COLOR] GetData()
    
[COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
[COLOR=darkblue]Dim[/COLOR] q [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
[COLOR=darkblue]Dim[/COLOR] FilePath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String
[/COLOR]Dim DstRng As Range
Dim c As Range
Dim xlw As Workbook
    
    [COLOR=darkblue]Set[/COLOR] DstRng = ThisWorkbook.Sheets("Table").Range("G2")
    q = 1
    DstRng.Offset(q, 0) = k [COLOR=green]'What is k?[/COLOR]
    
    FilePath = Application.GetOpenFilename("All Files (*.*),*.*")
    [COLOR=darkblue]If[/COLOR] FilePath = "False" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=green]'User clicked cancel[/COLOR]
    [COLOR=darkblue]Set[/COLOR] xlw = xlo.Workbooks.Open(FilePath)
    ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500").Value = xlo.Worksheets(3).Range("A1:M2500").Value
    xlw.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] Sheet1.Range("B1", Sheet1.Range("B" & Rows.Count).End(xlUp)) [COLOR=green]'B1 to last used row[/COLOR]
        v = Application.Match(c.Value, Sheet3.Range("B1:B2500"), [COLOR=darkblue]False[/COLOR])
        [COLOR=darkblue]If[/COLOR] IsNumeric(v) [COLOR=darkblue]Then[/COLOR]
            DstRng.Offset(q, 0) = Application.Sum(Sheet3.Range("I" & v).Resize(, 5))
            q = q + 1
        [COLOR=darkblue]Else[/COLOR]
            [COLOR=darkblue]If[/COLOR] MsgBox("No match for: " & v & vbLf & vbLf & "Continue?", _
               vbYesNo, "No Match Found") = vbNo [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] c
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Christoffer]

Finding the last used row was much needed, thanks.
Although I have some trouble getting the exception handling working and since the data I'm looking through isn't sorted I won't be able to use the match function.
Reverting back to the VLookup method I tried this for error handling:

Attempt 1:
If IsNumeric(Application.WorksheetFunction.VLookup(c.Value, ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500"), 8, False)) Then
Attempt 2:
If (Application.WorksheetFunction.VLookup(c.Value, ThisWorkbook.Sheets("PasteSheet").Range("B1:N2500"), 8, False) = True) Then

But none of those work, once the function tries to find a number with no matches it throws the error 1004.
Any idea on how to fix it?

 

[AlphaFrog]

I would stick with the MATCH function as suggested. Your data doesn't have to be sorted. It will find an exact match just like the VLOOKUP, and you only do one search for each term instead of 5 with VLOOKUP.

If you insist on using VLOOKUP, use the VBA function (not .Worksheetfunction) to trap the error. Try this syntax.
IF Not IsError(Application.VLookup(c.Value,...)) Then

 

[Christoffer]

I'll defer to your superior knowledge.
Using the match function works great once I changed the MsgBox from using the "v" variable to using the "c.Value" variable. It kept throwing a fit whenever there were no match(v = N/A)

Thanks for all your help, you've been great.

 

[AlphaFrog]

Yeah, I messed that bit up didn't I. Sorry. Glad you figured it out.

I till don't understand what this is for?

DstRng.Offset(q, 0) = k [COLOR="#008000"]'What is k?[/COLOR]

 

[Christoffer]

That's just some leftover code from my tinkering. Must have forgotten to erase it.