Custom VlookUp with range 2

[ItIsHardToProgram]

Hello once again everyone,

I am currently working on a Function for one of my worksheet. This is what the function should be doing:

Take a range of GL accounts delimited by "..", i.e. 40100..40200

With that range, look up in a tab to see if theres an account that fits between these 2.

Return the sum of all the values foud in the corresponding criteria.

This is what i built, and I believe it works, but I get a Type incompatibility, error 13, on my split.

Ive tried calling the array of the split, dosnt work, ive tried turning the type of variable that holds the split in a table, dosnt work... Basically I need to find out if I am doing anything wrong, maybe split won't work for this?


Here is my function:

N.B.* the function is currently a sub for testing (debug.prints etc...) the lower part of the sub in comment will also be part of the function.

Sub YlookUp( _
    GLrange As String, _
    location As String, _
    ActColumn As Integer)


On Error GoTo ErrorHandler


Dim LookUpSplit(2) As String
Dim GLtab As Long
Dim i As Integer
Dim LookUpSplit1 As Long
Dim LookUpSplit2 As Long
Dim FoundValue As Double
    
    'Délimitation des bornes de lookup
    [highlight]LookUpSplit = Split(GLrange, "..")[/highlight]
    
    
        

    For i = 1 To 10
        GLtab = Sheets("Accts").Cells(i, 1)
        
        'si un compte correspond au range, alors additionne le chiffre de la colone au reste
        If GLtab > LookUpSplit(1) And GLtab < LookUpSplit(2) Then
            FoundValue = FoundValue + Application.VLookup(GLtab, [b]Range(sheets.location)[/b], ActColumn, 0)
        End If
          
    Next i
    
       

    'YlookUp = FoundValue
   
    
           'If IsError(YlookUp) Then
            '    YlookUp = "Error"
             '   MsgBox ("You have encountered an error with value " & _
              '      GLrange & " " & _
               '     location)
           'End If

ErrorHandler:
    MsgBox (Err & " " & Error(Err))
    

End Sub

As you can see, the part highlighted is where it returns the error, Also, when the code is like this, it wont let me fire because LookUpSplit is a table, if i turn it in a value and scrap my LookUpSpliT(1) and (2) it returns an incompatibility error (13).


The 2nd part that is in bold is because I am not sure of how to call the range in a lookup, do I have to reference the sheet, like this: Sheets(location)

Location in this situation would be the following string 'My sheet'!$A$45:$B$55.


Thanks for your help every one.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Well after I was done testing I turned it in Function YlookUp( etc....) as double


At the end I palce

YlookUp = FoundValue

And I have a cell with

=YlookUp($A11,'OPERATING BDGT - LAUREA FORMAT'!$A$6:$P$168,5)

No joy, returns #Name, even though when its in a sub FoundValue returns 164963 as LONG...

Sigh =/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Is your FUNCTION CODE in a MODULE and not in a Sheet Object or the ThisWorkbook object?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

The function expect strings:
=YlookUp("$A11","'OPERATING BDGT - LAUREA FORMAT'!$A$6:$P$168",5)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

ThisWorkbook object

Ill try with manual data.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

As PHV pointed out...

Function YlookUp( _
    GLrange [b]As String[/b], _
    location [b]As String[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

In vb, from a sub calling the function:

a = YlookUp("40100..40999", "'OPERATING BDGT - LAUREA FORMAT'!$A$6:$P$168", 5)

a returns 194600

In excel, in a cell

=YlookUp("40100..40999", "'OPERATING BDGT - LAUREA FORMAT'!$A$6:$P$168", 5)

returns #NAME?

????

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Please post your completed code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

IN thisworkbook (general)

Option explicit

Sub ValCAll()
Dim a As String
Dim b As String
Dim c As Integer




a = YlookUp("40100..40999", "'OPERATING BDGT - LAUREA FORMAT'!$A$6:$P$168", 5)
MsgBox (a)
End Sub




Function YlookUp( _
    GLrange As String, _
    location As String, _
    ActColumn As Integer)

On Error Resume Next

Dim i As Integer
Dim FoundValue As Double
Dim GLtab As Long
Dim LookUpSplit
Dim SplitLocation
Dim SplitName
    
    'Délimitation des bornes de lookup
    LookUpSplit = Split(GLrange, "..")
    SplitLocation = Split(location, "!")
    SplitLocation(0) = Replace(SplitLocation(0), "'", "")
    

    For i = 1 To 10
        GLtab = Sheets("Accts").Cells(i, 1)
        
        'si un compte correspond au range, alors additionne le chiffre de la colone au reste
        If GLtab > Val(LookUpSplit(0)) And GLtab < Val(LookUpSplit(1)) Then
            FoundValue = FoundValue + Application.VLookup(GLtab, Sheets( _
            SplitLocation(0)).Range(SplitLocation(1) _
            ), ActColumn, 0)
            
        End If
          
    Next i
    
        
  
       

    YlookUp = FoundValue
   
    
           If IsError(YlookUp) Then
               YlookUp = "Error"
              MsgBox ("You have encountered an error with value " & _
                GLrange & " " & _
                location)
           End If


End Function

=/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

"IN thisworkbook (general)"

WHY???

Is your FUNCTION CODE in a [red]MODULE[/red] and not in a Sheet Object or the ThisWorkbook object?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I see.

Yep that fixed it, thank you skip, thank you PHV,

Sry for being a bit ignorant on the matter...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.