Using Named Ranges in VBA Excel 2007

Question

I am trying to access a named range from using VBA in Excel 2007. When I used the explicit range i.e. "A3:M53" I can get the formula below to work. However, when I replace the explicit range with a named range I get an error. I need to use named ranges as the program needs to select different ranges depending on what data is imported. Fixed.OAX.5 is a named range on the OAX50 worksheet.

WorksheetFunction.Match(5, Worksheets("OAX50").Range("Fixed.OAX.5"), 1)

When I run this code I get "Unable to get the Match property of the WorksheetFunction class.

user3598756 user3598756 · Accepted Answer · 2017-03-30T15:41:40

you're most probably not finding value 5 in Worksheets("OAX50").Range("Fixed.OAX.5")

use Application.Match() and wrap any possible error:

Dim x As Variant
With Worksheets("OAX50").Range("Fixed.OAX.5")
    x = Application.Match(5, .Cells, 1)
    If IsError(x) Then
        MsgBox "Sorry, no match for '5' in range " & .Address & " of worksheet '" & .Parent.Name & "'"
    Else
        'your code to exploit 'x'
    End If
End With

Using Named Ranges in VBA Excel 2007

1 Answers