Since my earlier post has gone in a different direction, I have created a very simple test of what I am trying to do. The worksheet with the
VB function is attached. Can someone please tell me what I am doing wrong? Thanks for all the help you all are giving me. I'm just not getting it.
I don't seem to be able to attach a file, so here is the sheet I have made:
The 4th/10th column contains Rater(Chart,A1,B1,C1) Rater(Chart2,G1,H1,I1)
Subsequent rows have the different row number.
Test 1 - Range Below Test 2 - Range Below
A C E #VALUE! 1 2 3 #VALUE!
A C F #VALUE! 2 3 1 #VALUE!
A C G #VALUE! 3 1 2 #VALUE!
A C H #VALUE! 4 5 6 #VALUE!
A C I #VALUE! 5 4 6 #VALUE!
B D J #VALUE! 6 5 4 #VALUE!
B D K #VALUE! 7 8 9 #VALUE!
B D L #VALUE! 8 9 7 #VALUE!
B D M #VALUE! 9 7 8 #VALUE!
B D N #VALUE! 10 9 8 #VALUE!
This test attempts to lookup text values. This test attempts to lookup numeric values.
Cells are formatted "General" since Cells are formatted "General" since
Function uses Variant. Function uses Variant.
Range is Chart Range is Chart2
10 10
CUST PICKUP DROP RATE CUST PICKUP DROP RATE
A C E 1 1 2 3 1
A C F 2 2 3 1 2
A C G 3 3 1 2 3
A C H 4 4 5 6 4
A C I 5 5 4 6 5
B D J 6 6 5 4 6
B D K 7 7 8 9 7
B D L 8 8 9 7 8
B D M 9 9 7 8 9
B D N 10 10 9 8 10
Here is the
VB Function I am using:
Code:
Public Function Rater(CRates, x As Variant, y As Variant, z As Variant)
Dim a As Integer
Dim last As Integer
Rater = 0
last = Worksheets("sheet1").Range(CRates).Cells(1, 1).Value
For a = 3 To last
If (x = Worksheets("sheet1").Range(CRates).Cells(a, 1).Value) Then
If (y = Worksheets("sheet1").Range(CRates).Cells(a, 2).Value) Then
If (z = Worksheets("sheet1").Range(CRates).Cells(a, 3).Value) Then
Rater = Worksheets("sheet1").Range(CRates).Cells(a, 4).Value
Exit Function
End If
End If
End If
Next a
End Function
:)
Excel/VBA Multi-Column Lookup - Round 2
