PDA

View Full Version : Multiple V-look ups at once



Djblois
02-22-2007, 11:28 AM
Right now I am doing multiple v-lookups to find information about a product and put it in our sales details. Is it possible to combine them? Here is an example of my current code

'Fix Division (V)
Detail.Range("V1").FormulaR1C1 = "Division"
Detail.Range("V2:V" & finalRow).FormulaR1C1 = _
"=VLOOKUP(RC[-12],'[BusinessReportingReference.xls]Products'!C1:C2,2,FALSE)"
Detail.Range("V2:V" & finalRow).Value = Detail.Range("V2:V" & finalRow).Value

'Fix Dept (W)
Detail.Range("W1").FormulaR1C1 = "Dept"
Detail.Range("W2:W" & finalRow).FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[BusinessReportingReference.xls]Products'!C1:C2,2,FALSE)"
Detail.Range("W2:W" & finalRow).Value = Detail.Range("W2:W" & finalRow).Value

'Fix Country (X)
Detail.Range("X1").FormulaR1C1 = "Country"
Detail.Range("X2:X" & finalRow).FormulaR1C1 = _
"=VLOOKUP(RC[-14],'[BusinessReportingReference.xls]Products'!C1:C6,6,FALSE)"
Detail.Range("X2:X" & finalRow).Value = Detail.Range("X2:X" & finalRow).Value

mdmackillop
02-22-2007, 12:11 PM
I don't see much point in trying to combine these.
If you're going to do this a lot, you could look at creating a function to which you pass the formula elements as parameters and which will return your formula string.

Djblois
02-22-2007, 12:22 PM
Would that be quicker in running and a smaller footprint? If so can you tell me more?

mdmackillop
02-22-2007, 12:48 PM
Here's what I mean.

Option Explicit

Sub Test()
Dim VLU As String
Dim VWB As String
Dim VWS As String
Dim VRng As String
Dim VOset As String

'VLU = "RC[-2]"
VWB = "Image"
VWS = "Products"
VRng = "C1:C6"
'VOset = 6
ActiveCell.Offset(, 3).FormulaR1C1 = VLOOK("RC[-2]", VWB, VWS, VRng, 5)
ActiveCell.Offset(, 4).FormulaR1C1 = VLOOK("RC[-3]", VWB, VWS, VRng, 6)
End Sub

Function VLOOK(VLU As String, VWB As String, VWS As String, VRng As String, VOset As String) As String
VLOOK = "=VLOOKUP(" & VLU & ",'[" & VWB & ".xls]" & VWS & "'!" & VRng & "," & VOset & ",FALSE)"
End Function

Djblois
02-23-2007, 08:08 AM
mdmackillop should that run quicker than the way I am doing it?

mdmackillop
02-23-2007, 08:33 AM
No, but it may be quicker to code if you use VLOOKUP a lot.

moa
02-23-2007, 08:49 AM
You could use application.WorksheetFunction.VLookup to do your look ups and put the result into the cell rather than the formula, if you are just going to get rid of the formula afterward.

Don't know if it's quicker.