0

I'm having a problem defining a range on a separate sheet for an array that I have for a function that I'm running. The function is KVLOOKUP and can be found here. The problem I'm having is when I try to set the Range in Sub Searched it won't accept any type of defined range. I've tried what I can think of to define the range in different ways. Not sure where I'm going wrong here.

Function to be Called

Function KVLOOKUP(LookedValue As String, Matrix As Variant, Column As Integer) As Variant

 Dim Result() As Variant
 Dim i As Integer
 Dim Counter As Long
 Dim Column1 As Integer
 Column1 = Column + 1

 If IsObject(Matrix) Then Matrix = Matrix.Value
 On Error Resume Next
 Do
  i = i + 1
 Counter = UBound(Matrix, i)
 Loop Until Err.Number <> 0

 If Counter < Column Then KVLOOKUP = CVErr(xlErrNum): Exit Function
 Counter = 0

 For i = LBound(Matrix, 1) To UBound(Matrix, 1)
 If Matrix(i, 1) = LookedValue Then
 Counter = Counter + 1
 ReDim Preserve Result(1 To Counter)
 Result(Counter) = Matrix(i, Column) & " - " & Matrix(i, Column1)



 End If

 Next i

 On Error GoTo 0

 If Counter = 0 Then
    KVLOOKUP = CVErr(xlErrNA)
 Else
    KVLOOKUP = Result(1)
    For i = 2 To UBound(Result)
        KVLOOKUP = KVLOOKUP & ", " & Result(i)
    Next i
 End If
End Function

My problem is within the code below. In a cell I can call KVLOOKUP like a normal function =KVLOOKUP(TextToSearch,'IDBHour1'!B2:E120,2). When I have attempted to define the same range by calling the function within VBA I haven't been able to define the same range as above.

Sub to define E_name and Rnge Values

For some reason if I try to define the range like this (which is how it would be done for a normal VLookup function) it doesn't provide any results. This is how I've been going about debugging my issue.

When I run the code I get an "Object variable or With Block variable not set" error

Sub SearcherBox()
'E_name is just a name i.e., John Doe
'Rnge is the range in which KVLOOKUP is searching for "John Doe"
 Dim Rnge as Range 
  Rnge = Sheets("IDBHour1").Range("B2:E120")
  Sal = Application.WorksheetFunction.KVLOOKUP(E_name, Rnge, 2)
   MsgBox Sal
End Sub
Community
  • 1
  • 1
ryano
  • 231
  • 1
  • 5
  • 18
  • 1
    Could you please narrow down your question just to problematic part (no need to post the entire code)? Thanks and regards, – Alexander Bell Jun 30 '14 at 20:38
  • *it won't accept any type of defined range* What does thsi mean? Do you get an error? If so, what is the error? – David Zemens Jun 30 '14 at 20:40
  • Also, it would be worth [advising you against](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) using `Selection.Value` **ESPECIALLY** when doing more advanced programming involving user forms, and multiple worksheets, etc... – David Zemens Jun 30 '14 at 20:42
  • @DavidZemens No data is returned at all. Even though the range that I'm trying to set for the array will work as a normal function within a cell on the sheet, when I try to achieve the same goal in VBA form it doesn't collect any results. – ryano Jun 30 '14 at 20:43
  • @AlexBell I've cleaned it up a bit. Hopefully you can understand what my issue is a little easier. Let me know if it's still not clear. – ryano Jun 30 '14 at 20:58
  • @ryano if you know that `Matrix` is a range, why are you defining it as a `variable`? – LBPLC Jun 30 '14 at 21:03
  • I think you cleaned up too much. Now we have no idea what actually transpires within your `KVLOOKUP` function. When you debug this (you have debugged, right?) what are the value of `LookedValue`? Is it what you expect it should be? Same question for the other two arguments -- have you ensure to pass the correct arguments? Also **GET RID OF ON ERROR RESUME NEXT** as that may be "hiding" the source of your problem. – David Zemens Jun 30 '14 at 21:07
  • @SilverShotBee Maybe I'm not understanding the question but I think it's because there's a problem in the original KVLOOKUP code if I change it to a range. – ryano Jun 30 '14 at 21:08
  • @DavidZemens Ok I've added JUST my debugging script. Using this method I haven't been able to successfully get any return values. – ryano Jun 30 '14 at 21:15
  • `Rnge` is an object variable. Object variables require using the `Set` keyword on assignment, so `SET Rnge = Sheets("IDBHour1").Range("B2:E120")` :) – David Zemens Jun 30 '14 at 21:21
  • @DavidZemens Ok, but now I'm getting an "Object doesn't support this property or method" error when I execute. – ryano Jun 30 '14 at 21:24
  • do I have to ask you again what line raises this new error? – David Zemens Jun 30 '14 at 21:28
  • @DavidZemens `Sal = Application.WorksheetFunction.KVLOOKUP(E_name, Rnge, 2)` – ryano Jun 30 '14 at 21:29
  • `KVLOOKUP` is not a `WorksheetFunction`... call it simply like `Sal = KVLOOKUP(E_name, Rng, 2)`. – David Zemens Jun 30 '14 at 21:30
  • @DavidZemens Thanks for helping me through that I got it working now. – ryano Jun 30 '14 at 21:41

1 Answers1

1

A few things:

Rnge is an object variable. Object variables require using the Set keyword on assignment, so

SET Rnge = Sheets("IDBHour1").Range("B2:E120")

Further, your UDF KVLOOKUP is not a WorksheetFunction and therefore not a member of the WOrksheetFunction class, so you get an error on the next line, too. Call your UDF simply as:

Sal = KVLOOKUP(E_name, Rnge, 2)
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • I always forget to `SET` a range, that and `End If`... If `KVLOOKUP` is not a WorksheetFunction class, why are you telling the OP to call it like this `Sal = Application.WorksheetFunction.KVLOOKUP(E_name, Rnge, 2)`? (Just trying to understand if its a mistake, or if I'm being silly) – LBPLC Jun 30 '14 at 22:23