Custom VlookUp with range 2

ItIsHardToProgram · Jul 29, 2008

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.

Code:

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.

SkipVought · Jul 29, 2008

Code:

Dim LookUpSplit[s](2) As String[/s]

must be declared as a VARIANT, not a string, not an array.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

ItIsHardToProgram · Jul 29, 2008

Still no joy, heres what I have in my cell, and the modified function to fit what I believe should work

Code:

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

A11 = 40100..40999

Heres my function:

Code:

SplitLocation(0) returns 'OPERATING BDGT - LAUREA FORMAT'
SplitLocation(1) returns $A$32:$A$35
SplitLookUp(0) returns 40100
splitLookUp(1) returns 40999

FoundValue returns 0
A little bit better than I was doing earlier.

I believe im not referencing the Application.VlookUp as I should be?

The error returned is the number 9, I don't know how to translate it from french though

Thanks alot skip.

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

ItIsHardToProgram · Jul 29, 2008

oups, heres the function:

Code:

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

On Error Resume Next


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

    For i = 1 To 10
        GLtab = Sheets("Accts").Cells(i, 1)
        Debug.Print GLtab
        
        '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
    
       Debug.Print FoundValue
       

    '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

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

ItIsHardToProgram · Jul 29, 2008

Debug.Print Sheets(SplitLocation(0)).Range(SplitLocation(1)).Value

This returns nothing, what do I do from there.... =/

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

SkipVought · Jul 29, 2008

You have a problem here too...

Code:

        If GLtab > LookUpSplit([b]0[/b]) And GLtab < LookUpSplit([b]1[/b]) Then
            FoundValue = FoundValue + Application.VLookup(GLtab, Range([b][red]Sheets.location[/red][/b]), ActColumn, 0)
        End If

the LOWER bound of the array is ZERO.

Also [red]Sheets.location[/red].

You have location defined as a string, and not an object. What value are you passing?

How are you defining Sheet?

The correct syntax is

Code:

SheetObject.Range(AddressString)

Your logic is convoluted.  Don't understand what you are trying to do.  It is a poor workbook design to have similare data in different locations (tabs).  You inhibit the rich capabilities of Excel by destroying your source data!!!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

ItIsHardToProgram · Jul 29, 2008

Ok

This is whats going on.

We have benchmarks that come from a third party company, they give us protected workbooks that we can't change or do anything with.

I on my part build an extract that gets all the numbers I need, sum of accounts, so that I can extract them in another file where I will have a pivot table.

As you can see in teh 2nd post I have added I do not do Sheets.location but

Code:

Sheets(SplitLocation(0)).Range(SplitLocation(1))

and I also changed the arrays noticing my error,

Basicaly the look up sums up the range of accounts, because this is how we pull data for our DATABASE and I believe keeping the same logic is better.

the sheet is defined with a range as following:

'OPERATING BDGT - LAUREA FORMAT'!$A$6:$P$168

in the YlookUp() that is in a cell...

Thats all I can see to help you figure this out =/

Sry if I have a flawed way of programming/thinking/managing data but I do what I can with what I have, and its not programming courses.

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

SkipVought · Jul 29, 2008

Why do you think that this would return anything

Code:

LookUpSplit = Split("40100..40200", "..")

Sheets(SplitLocation(0)).Range(SplitLocation(1)).Value

Do you have a sheet named 40100 AND a range named 40200???

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

ItIsHardToProgram · Jul 29, 2008

Looks like you missed out on my new code:

Code:

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

On Error Resume Next


Dim LookUpSplit
Dim GLtab As Long
Dim i As Integer
Dim SplitLocation
Dim FoundValue As Double
    
    'Délimitation des bornes de lookup
    LookUpSplit = Split(GLrange, "..")
    [highlight]SplitLocation = Split(location, "!")[/highlight]
    Debug.Print LookUpSplit(0)
    Debug.Print LookUpSplit(1)
     Debug.Print "location"
     Debug.Print SplitLocation(0)
     Debug.Print SplitLocation(1)

    For i = 1 To 10
        GLtab = Sheets("Accts").Cells(i, 1)
        Debug.Print GLtab
        
        '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
    
       Debug.Print FoundValue
       

    '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

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

SkipVought · Jul 29, 2008

There is a problem here. The SINGLE QUOTES, will be parsed into the sheet name, when they are, in reality NOT part of the name. You must remove these.

Code:

SplitLocation=split("'My sheet'!$A$45:$B$55","!")
Debug.Print Sheets(SplitLocation(0)).Range(SplitLocation(1)).Value

ALSO, the Debug statement will NOT return a value on a range.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

ItIsHardToProgram · Jul 29, 2008

So now I removed the single quotes, but I get an Error 13, type incompatibility.

Code:

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

On Error Resume Next

Dim i As Integer

Dim GLtab As Long
Dim LookUpSplit
Dim SplitLocation
Dim SplitName
Dim FoundValue As Double
    
    'Délimitation des bornes de lookup
    LookUpSplit = Split(GLrange, "..")
    SplitLocation = Split(location, "!")
    SplitName = Split(SplitLocation, "'")
    SplitLocation(0) = SplitName(1)
    
    
    For i = 1 To 10
        GLtab = Sheets("Accts").Cells(i, 1)
        Debug.Print GLtab
        
        '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
            [b]Debug.Print "you made it in"[/b]
            [Highlight]FoundValue = FoundValue + Application.VLookup(GLtab, Sheets( _
            SplitLocation(0)).Range(SplitLocation(1) _
            ), ActColumn, 0)[/highlight]
            Debug.Print FoundValue
        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

This obviously is not working for a reason or another

I removed the debug to clarify this for you.

Everything else seems to be working just fine....

Any ideas???

Thanks skip

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

SkipVought · Jul 29, 2008

FoundValue is DOUBLE.

What is Application.VLookup(GLtab, Sheets( _
SplitLocation(0)).Range(SplitLocation(1) _
), ActColumn, 0)
returning?

Use the Watch Window if necessary. faq707-4594

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

ItIsHardToProgram · Jul 29, 2008

It dosnt return anything.

The Error 13 is on that statement.

If it was to return anything it would be a number and the cell has a number format.

Something must not be correct.

ActColumn is 5
Gltab returns a valid account (40500)

The range dosnt have quote and is has stated...

What would make the application not fire up?

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

PHV · Jul 29, 2008

I'd replace this:
SplitName = Split(SplitLocation, "'")
SplitLocation(0) = SplitName(1)
with this:

Code:

SplitLocation(0) = Replace(SplitLocation(0), "'", "")

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

ItIsHardToProgram · Jul 29, 2008

Type is variant/empty

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

ItIsHardToProgram · Jul 29, 2008

There arent any errors anymore, but still nothing returned.

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

ItIsHardToProgram · Jul 29, 2008

Somehow the SplitLocation(0) = Replace(SplitLocation(0), "'", "") fixed it, what would be the reason of that?

Thanks all,

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

SkipVought · Jul 29, 2008

You can use the Watch Window to investigate each object in your statement, starting with the most significant. That will show you where your problem is.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

ItIsHardToProgram · Jul 29, 2008

The function still does not return what I want even though FoundValue does return what I want....

When I place this in a function, and i call teh function with arguments in my Worksheet, its not working...

Sigh....

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

SkipVought · Jul 29, 2008

What function???

Code:

[B]
Sub[/B] YlookUp( _
    GLrange As String, _
    location As String, _
    ActColumn As Integer)

....

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Custom VlookUp with range 2

Technical User

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

MIS

Technical User

Technical User

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor