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
'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