CODEWITHSUNDEEP
vbaexcel
physics90
physics90

Reputation: 963

Using Named Ranges in VBA Excel 2007

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.

Upvotes: 1

Views: 503

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 2

Related Questions