CODEWITHSUNDEEP
vbaexcel
CatParky
CatParky

Reputation: 189

VBA Referring to named range

I'm just starting to used named references within my VBA, but have hit a minor problem.

I have a named reference called "SiteCount" that counts the number of sites in a column 

=COUNTA(Data!$B:$B)

It is saved as Workbook in scope and works when I use it in a cell as 

=SiteCount

I thought I could use this in my code rather than replicate the calculation, however I can not save the value even just as 

Sitecount = Range("SiteCount")

I have tried using the worksheet name as well, but I get the same 1004 "Method range of object global failed"

I'm guessing it's something very simple. but I can't figure it out. Advice is gratefully received :)

Upvotes: 3

Views: 743

Answers (3)

Rory
Rory

Reputation: 34075

It's not a named range, it's a named formula, so you have to refer to it differently. You can use:

lSiteCount = [SiteCount]

note that the variable name must not be the same as the formula name!

or

SiteCount = Sheets("Data").Evaluate("SiteCount")

or

SiteCount = Application.Evaluate("SiteCount")

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96781

If you want to get the values of specific named formulas then:

Sub WhatsInAName()
    For Each n In ActiveWorkbook.Names
        If n.Name = "SiteCount" Then
            MsgBox n.Value & vbCrLf & Evaluate(n.Value)
        End If
    Next n
End Sub

Upvotes: 1

user2140173
user2140173

Reputation:

Evaluate() should do since it's a formula name not a named range

Dim siteCount as Long
siteCount = Evaluate("SiteCount")

Upvotes: 3

Related Questions