"Unable to get the Hlookup property" VBA

[ExcelMonkey]

Can't get this to work. I am trying to look up a value in the first
dimension of an array called NumberSetArray. Keep getting the "Unable to get
the Hlookup property" error.

?Application.WorksheetFunction.HLookup(7, NumberSetArray, 1, False)

I know the array has a 7 in it first dimension as seen below.
?NumberSetArray(0,0)
7


Is the problem due to the third term in the Hlookup Function?

Thanks

EM

 

[Niek Otten]

I get this if the value to be looked up isn't present.

Do you really mean to look up the 7 itself?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Can't get this to work. I am trying to look up a value in the first
| dimension of an array called NumberSetArray. Keep getting the "Unable to get
| the Hlookup property" error.
|
| ?Application.WorksheetFunction.HLookup(7, NumberSetArray, 1, False)
|
| I know the array has a 7 in it first dimension as seen below.
| ?NumberSetArray(0,0)
| 7
|
|
| Is the problem due to the third term in the Hlookup Function?
|
| Thanks
|
| EM
|
|

 

[ExcelMonkey]

Yes. I am looking up a value in an array that is getting bigger after each
successive loop. There will be instances where the value is in fact not in
the array. I think I need to wrap some error handling around it so that if
it fails it does not stop the code.

EM

 

[ExcelMonkey]

I am thinking along the lines of something like this:

NumExists = Application.WorksheetFunction.HLookup(RowNumberArray(X),
NumberSetArray, 2, False)
If Not IsError(NumExists) Then
Do Something
End if
Not sure if I need an On Error/Resume stmt to make this work.

EM

 

[ExcelMonkey]

Here is the dilemma. The Hlookup should at time find no value. However when
this happens the code fails stating Unable to get the Hlookup property. I
put an On Error stmt in do that the code would still loop. The hope being
that the IsErorr function would still pick up the error even thoughe code is
looping. However, the IsError stmt is always TRUE indicating there is never
an error which I know is not correct. How do I incorporate error handling in
this code while using a Hlookup which I expect to fail given the underlying
data in the array that the Hlookup anlyazes?

On Error Resume Next
NumExists = Application.WorksheetFunction.HLookup(RowNumberArray(X),
NumberSetArray, 2, False)
If Not IsError(NumExists) Then
Do Something
End if

Thanks

EM

 

[ExcelMonkey]

Maybe this is my problem. My array is set up with 2 rows and multiple
columns. I am using a hlookup maybe I should be using a vlookup. Will look
into it

 

[JMB]

This works fine for me. It matches to element (1,5) and would return element
(2,5) by changing the third Hlookup argument to 2.

For some reason, you get an error when using
Application.WorksheetFunction.HLookup or Worksheetfunction.Hlookup (and would
need to use on error resume next), but you can use Application.Hlookup and
test w/Iserror without disabling error handling. Your variable used to store
the value returned would need to be a variant. Don't know why it works that
way, but it is the same issue for Vlookup.

Sub test()
Dim NumberSetArray(1 To 2, 1 To 5) As Long
Dim varNumExists As Variant

NumberSetArray(1, 1) = 1
NumberSetArray(1, 2) = 2
NumberSetArray(1, 3) = 3
NumberSetArray(1, 4) = 4
NumberSetArray(1, 5) = 7
NumberSetArray(2, 1) = 10
NumberSetArray(2, 2) = 20
NumberSetArray(2, 3) = 30
NumberSetArray(2, 4) = 40
NumberSetArray(2, 5) = 50

varNumExists = Application.HLookup(7, NumberSetArray, 1, False)
If Not IsError(varNumExists) Then
MsgBox "Number Exists"
Else: MsgBox "Number Does Not Exist"
End If

End Sub

 

[ExcelMonkey]

You're right your example works. Mine still doesn't. Here are the variable
results in the immediate window:

?NumberSetArray(0,0)
7
?NumberSetArray(1,0)
49
?RowNumberArray(X)
8
?X
1

Note I am using variant types for everything. Can't figure out why this is
not working. What causes the "Unable to get Hlookup property" error in the
first place?

Here is the code:

Dim RowNumberArray As Variant
Dim NumberSetArray As Variant
Dim NumOccurences As Variant
Dim NumExists As Variant
Dim Counter As Integer
Dim Counter2 As Integer

ReDim RowNumberArray(0 To UBound(SummaryArrayCleanTrans))

For X = 0 To UBound(SummaryArrayCleanTrans)
RowNumberArray(X) = StripNonNumerics(SummaryArrayCleanTrans(X, 2))
Debug.Print RowNumberArray(X)
Next

'Break out string into individual number sets
Counter = 0
'Invert Array and then transpose later
ReDim NumberSetArray(0 To 1, 0 To 0)
For X = 0 To UBound(RowNumberArray)
If X = 0 Then
NumberSetArray(0, X) = RowNumberArray(X)
Counter2 = 0
For i = 0 To UBound(RowNumberArray)
If RowNumberArray(X) = RowNumberArray(i) Then
Counter2 = Counter2 + 1
End If
Next i
NumOccurences = Counter2
NumberSetArray(1, X) = NumOccurences
Counter = Counter + 1
Else
'Check to see if row number already exists
NumExists = Application.WorksheetFunction.HLookup(RowNumberArray(X),
NumberSetArray, 1, False)
If Not IsError(NumExists) Then

 

[ExcelMonkey]

Ahhhhhhhhhhh!

The reason it works in your example is that you are using the following
syntax:
Application.Hlookup()

I am using:
Application.WorksheetFunction.Hlookup()

As per wisdom of the Daily Dose of Excel
"In VBA, the WorksheetFunction method throws a run time error when this
happens. The error is trappable, so you can use an On Error statement to
avoid it, but there’s a better way. If you use the function as a method of
the Application object directly, and dimension your variable as a Variant,
the variable will hold the error value and no error will occur."

I have always known you can do both but did not really understand the
pros/cons.

You can read the article here:
http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

Thanks for you efforts!

EM