Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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 | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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 | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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 | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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 | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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 | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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" wrote: 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 | | |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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(RowNumberArr ay(X), NumberSetArray, 1, False) If Not IsError(NumExists) Then "JMB" wrote: 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" wrote: 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 | | |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Unable to get the Hlookup property" VBA
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 theres 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 he http://www.dailydoseofexcel.com/arch...nction-method/ Thanks for you efforts! EM "JMB" wrote: 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" wrote: 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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Unable to get Pivot Tables Property of Worksheet Class "Error | Excel Programming | |||
Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class" | Excel Programming | |||
Interop - "Unable to set the Color property of the Interior class" | Excel Programming | |||
"Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
"Unable to get the Pivotfieldsd property of the Pivot Class" | Excel Programming |