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.

Try removing the sheet reference.

SJR
– SJR

2017-03-30 15:41:17 +00:00
Commented Mar 30, 2017 at 15:41 — SJR
– SJR, Commented Mar 30, 2017 at 15:41

user3598756 · Accepted Answer · 2017-03-30 15:41:40Z

2

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

answered Mar 30, 2017 at 15:41

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3598756 Over a year ago

@physics90, did you get through it?

physics90 Over a year ago

I'm about to try it with your suggestions.

physics90 Over a year ago

that worked. Thanks a bunch. But why? Why can't I use worksheetfunction.match or worksheetfunction.index? And why can't I use the reference directly within the equation?

user3598756 Over a year ago

Using the WoksheetFunction version of those methods brings along the need of handling errors should those method fail finding what you tell them to search for. While the Application version wraps the error in the Variant return value that you can query with the IsError() function

physics90 Over a year ago

The thing is there weren't any errors. The value 5 is in the table and was found when I switched to your method.

|

Collectives™ on Stack Overflow

Using Named Ranges in VBA Excel 2007

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related