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

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

"ExcelMonkey" wrote in message ...
| 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
|
|

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

"Niek Otten" wrote:

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

"ExcelMonkey" wrote in message ...
| 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
|
|

I am thinking along the lines of something like this:

NumExists = Application.WorksheetFunction.HLookup(RowNumberArr ay(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" wrote:

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

"Niek Otten" wrote:

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

"ExcelMonkey" wrote in message ...
| 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
|
|

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(RowNumberArr ay(X),
NumberSetArray, 2, False)
If Not IsError(NumExists) Then
Do Something
End if

Thanks

EM


"ExcelMonkey" wrote:

I am thinking along the lines of something like this:

NumExists = Application.WorksheetFunction.HLookup(RowNumberArr ay(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" wrote:

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

"Niek Otten" wrote:

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

"ExcelMonkey" wrote in message ...
| 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
|
|

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

"ExcelMonkey" wrote:

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(RowNumberArr ay(X),
NumberSetArray, 2, False)
If Not IsError(NumExists) Then
Do Something
End if

Thanks

EM


"ExcelMonkey" wrote:

I am thinking along the lines of something like this:

NumExists = Application.WorksheetFunction.HLookup(RowNumberArr ay(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" wrote:

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

"Niek Otten" wrote:

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

"ExcelMonkey" wrote in message ...
| 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
|
|