Hlookup

[mcmichael13]

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

[Kenneth Hobs]

I gather your problem is with hlookup. This is because your last did not pass values to the 1st and 3rd parameter.
e.g.
Sub BinName()
Dim BinName(1 To 10) As String
With Worksheets("Part Info")
BinName(1) = WorksheetFunction.HLookup(.Range("$C$3").Value, _
.Range("$C$34:$J$61"), .Range("$A$53").Value, False)
End With
Debug.Print BinName(1)
End Sub

[mcmichael13]

the debug is not even displaying a window... hmm

[mcmichael13]

thanks for your help so far... i've used


Public Sub BinName()
Dim BinName(1 To 10) As String
With Worksheets("Part Info")
BinName(1) = WorksheetFunction.HLookup(.Range("$C$3").Value, _
.Range("$C$34:$J$61"), 19, False)
BinName(2) = WorksheetFunction.HLookup(.Range("$C$3").Value, _
.Range("$C$34:$J$61"), 20, False)
BinName(3) = WorksheetFunction.HLookup(.Range("$C$3").Value, _
.Range("$C$34:$J$61"), 21, False)
End With
MsgBox BinName(1)
MsgBox BinName(2)
MsgBox BinName(3)
End Sub


and the bin names are correct, however they will not input in the cells which they should. something wrong with the for loop in the function?

[Kenneth Hobs]

Not sure what you mean by input. The example that I gave works. Be sure that you can do it with a standard =hLookup first.

Post your xls with sample data for us to test if you like.

[mcmichael13]

well i guess i was working on things that he didnt need anymore, just talked to him and he forgot to mention he fixed it already.

thanks anyway