mcmichael13
05-18-2009, 07:04 AM
So my boss came to me needing a solution.
He wants to use Hlookup in vba. We have a set of coordinates being input to one sheet, and those are used to test which "bin" the coordinate falls into. We have about 7 bins to which it may be placed. However with different parts, the bins change, so what we did was create a table of bin names associated with the part number.
Now what we need the code to do is redefine the bin names given the part number selected.
Static Function BinTest(u, v)
'Sets which bins to test, BIN's are names,
'then checks to see if point is in bin.
Dim BinToTest As Range
Dim b As Integer
Dim y As String
Dim z As String
Dim BinName(1 To 10) As String
'BinName(1) = "L"
'BinName(2) = "M"
'BinName(3) = "WT"
y = "BIN"
'Set which bin to test.
'CONCATENATE combins "BIN" and the value of b to create BIN1, BIN2, ect.
For b = 1 To [NumOfBins]
Set BinToTest = Range(Evaluate("CONCATENATE(""" & y & """,""" & b & """)"))
If udfPointInPolygon(u, v, BinToTest, 100) Then
BinTest = BinName(b)
Exit For
Else
If b = [NumOfBins] Then
BinTest = "000"
Exit For
End If
End If
Next b
End Function
That is the naming of the bins and the following is our attempts at Hlookup, to retrieve the information from the table located on a different sheet.
Static Sub BinName()
Worksheets("Part Info").Activate
Dim BinName(1 To 10) As String
BinName(1) = Application.WorksheetFunction.HLookup(Range("$C$3"), Range("$C$34:$J$61"), Range("$A$53"), False)
BinName(2) = Application.WorksheetFunction.HLookup(Range("$C$3"), Range("$C$34:$J$61"), Range("$A$54"), False)
BinName(3) = Application.WorksheetFunction.HLookup(Range("$C$3"), Range("$C$34:$J$61"), Range("$A$55"), False)
End Sub
The "000" works, but if the part actually falls into one of those 3 bins (just for testing) the field comes up blank on the sheet where the bin name is displayed.
any suggestions?
let me know if you need more info.
Thanks
He wants to use Hlookup in vba. We have a set of coordinates being input to one sheet, and those are used to test which "bin" the coordinate falls into. We have about 7 bins to which it may be placed. However with different parts, the bins change, so what we did was create a table of bin names associated with the part number.
Now what we need the code to do is redefine the bin names given the part number selected.
Static Function BinTest(u, v)
'Sets which bins to test, BIN's are names,
'then checks to see if point is in bin.
Dim BinToTest As Range
Dim b As Integer
Dim y As String
Dim z As String
Dim BinName(1 To 10) As String
'BinName(1) = "L"
'BinName(2) = "M"
'BinName(3) = "WT"
y = "BIN"
'Set which bin to test.
'CONCATENATE combins "BIN" and the value of b to create BIN1, BIN2, ect.
For b = 1 To [NumOfBins]
Set BinToTest = Range(Evaluate("CONCATENATE(""" & y & """,""" & b & """)"))
If udfPointInPolygon(u, v, BinToTest, 100) Then
BinTest = BinName(b)
Exit For
Else
If b = [NumOfBins] Then
BinTest = "000"
Exit For
End If
End If
Next b
End Function
That is the naming of the bins and the following is our attempts at Hlookup, to retrieve the information from the table located on a different sheet.
Static Sub BinName()
Worksheets("Part Info").Activate
Dim BinName(1 To 10) As String
BinName(1) = Application.WorksheetFunction.HLookup(Range("$C$3"), Range("$C$34:$J$61"), Range("$A$53"), False)
BinName(2) = Application.WorksheetFunction.HLookup(Range("$C$3"), Range("$C$34:$J$61"), Range("$A$54"), False)
BinName(3) = Application.WorksheetFunction.HLookup(Range("$C$3"), Range("$C$34:$J$61"), Range("$A$55"), False)
End Sub
The "000" works, but if the part actually falls into one of those 3 bins (just for testing) the field comes up blank on the sheet where the bin name is displayed.
any suggestions?
let me know if you need more info.
Thanks