Trouble using index/match on add-in worksheets

[kylemd]

Hi there! Apologies in advance for typos and rambling.

So I am currently working on a tax formula as a spare time project. The function takes the year and income figure, and calculates tax based on a few variables.

I have stored these variables in separate worksheets, each named as per what year they pertain to. The idea was that I could pass on the year the user input as the sheet name, and retrieve data from that sheet.

However, I'm having some issues doing that. The debugger stops at the line I have bolded, with no further information as to what error has occurred.

Here's what I have so far (snippet obviously) - I adapted @AlphaFrog's response here as it was a bit cleaner than index match.

'Income taxFunction IncTax(Year, TaxableIncome)


    Dim lb As Double        'Lower bracket
    Dim ls As Double        'Lump sum
    Dim r As Single         'Rate


'Get relevant values from worksheets
    With ThisWorkbook.Worksheets(Year)
[B]        lb = .Range("A" & Application.Match(TaxableIncome, Range("B:B"), -1))[/B]
    End With
    
    With ThisWorkbook.Worksheets(Year)
        ls = .Range("C" & Application.Match(TaxableIncome, Range("B:B"), -1))
    End With
    
    With ThisWorkbook.Worksheets(Year)
        r = .Range("D" & Application.Match(TaxableIncome, Range("B:B"), -1))
    End With
    
'Perform final calculation
    IncTax = (ls + ((TaxableIncome - lb) * r))


End Function

Where am I going wrong? I have a feeling it's got something to do with the way I'm referencing the sheets, but not certain.

 

[CodeNinja]

Try Application.WorksheetFunction.Match not Application.Match...

Good luck,

CN.

 

[kylemd]

Try Application.WorksheetFunction.Match not Application.Match...

Good luck,

CN.

Thanks for your quick reply CodeNinja!

I had tried Application.WorksheetFunction.Match before posting, which yielded the same result. With breakpoints on each match line, the code reaches the first one and halts.

I've made the change nonetheless though. I've also combined all three With statements - not sure how I missed that!

'Income tax
Function IncTax(Year, TaxableIncome)


    Dim lb As Double        'Lower bracket
    Dim ls As Double        'Lump sum
    Dim r As Single         'Rate


'Get relevant values from worksheets
    With ThisWorkbook.Worksheets(Year)
        lb = .Range("A" & Application.WorksheetFunction.Match(TaxableIncome, Range("B:B"), -1))
        ls = .Range("C" & Application.WorksheetFunction.Match(TaxableIncome, Range("B:B"), -1))
        r = .Range("D" & Application.WorksheetFunction.Match(TaxableIncome, Range("B:B"), -1))
    End With
    
'Perform final calculation
    IncTax = (ls + ((TaxableIncome - lb) * r))


End Function

Just in case below are some screenshots of the relevant worksheets also.

 

[kylemd]

With breakpoints on each match line, the code reaches the first line and halts.

Can't edit my post, this line is wrong. Oops. Should be

With breakpoints on each line including With ThisWorkbook.Worksheets(Year), the code reaches the first line and halts.

 

[Norie]

All the ranges in your Match functions will refer to the active sheet, not ThisWorkbook.Worksheets(Year).

Try this.

'Income tax

Function IncTax(Year, TaxableIncome)


    Dim lb As Variant        'Lower bracket
    Dim ls As Variant        'Lump sum
    Dim r As Variant         'Rate


'Get relevant values from worksheets
    With ThisWorkbook.Worksheets(Year)
        lb = .Range("A" & Application.Match(TaxableIncome, .Range("B:B"), -1))
    End With
    
    With ThisWorkbook.Worksheets(Year)
        ls = .Range("C" & Application.Match(TaxableIncome, .Range("B:B"), -1))
    End With
    
    With ThisWorkbook.Worksheets(Year)
        r = .Range("D" & Application.Match(TaxableIncome, .Range("B:B"), -1))
    End With
    
'Perform final calculation
    IncTax = (ls + ((TaxableIncome - lb) * r))

End Function

Or perhaps something like this.

Function IncTax(Year, TaxableIncome)
Dim lb As Variant        'Lower bracket
Dim ls As Variant        'Lump sum
Dim r As Variant         'Rate
Dim Res As Variant

    With ThisWorkbook.Worksheets(Year)
    
        Res = Application.Match(TaxableIncome, .Range("B:B"), -1)

        If Not IsError(Res) Then

            'Get relevant values from worksheets
            lb = .Range("A" & Res).Value
            ls = .Range("C" & Res).Value
            r = .Range("D" & Res).Value

            'Perform final calculation
            IncTax = (ls + ((TaxableIncome - lb) * r))
        Else
            IncTax = CVErr(xlErrNA)
        End If
        
    End With
    
End Function

 

[kylemd]

All the ranges in your Match functions will refer to the active sheet, not ThisWorkbook.Worksheets(Year).

Hi @Norie! You are correct - I was missing the "." in front of each Range. Thanks so much!

Everything is working as intended now, there was a few other things I needed to change:

• Passing the variable Year as the worksheet name didn't work. I had to convert it to string y first.
• As I was using a greater than match formula the data set needed to be sorted largest to smallest.

Here is the final code I've ended up with. Thanks again to @CodeNinja and @Norie for your help!

'Income tax
Function IncTax(Year, TaxableIncome)


'Variables for calculation
    Dim lb As Variant                       'Lower bracket
    Dim ls As Variant                       'Lump sum
    Dim r As Variant                        'Rate
    
'Variables to run query
    Dim y As String: y = Year               'Sheet name string


'Get relevant values from worksheets
    With ThisWorkbook.Worksheets(y)
    
        Res = Application.WorksheetFunction.Match(TaxableIncome, .Range("B:B"), -1)
        
        If Not IsError(Res) Then
            lb = .Range("A" & Res).Value
            ls = .Range("C" & Res).Value
            r = .Range("D" & Res).Value
        
        'Perform final calculation
            IncTax = (ls + ((TaxableIncome - lb) * r))
            
        Else
            IncTax = CVErr(xlErrNA)
        End If
        
    End With


End Function