Christoffer
New Member
- Joined
- Jun 21, 2013
- Messages
- 18
Hello
I'm having some trouble getting the VLookup function to work cross worksheets.
Current code:
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)
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?
I'm having some trouble getting the VLookup function to work cross worksheets.
Current code:
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)
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, 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?