CODEWITHSUNDEEP
excelvba
Jabberwocky
Jabberwocky

Reputation: 47

Set range and define named range in VBA

I am trying to define a named range (with Workbook scope) in VBA based on certain inputs to help figure out the cells that should be included in the range. To make it really simple, I have tried the following two with the errors indicated below. A, B, X, Y are found from some calculations

rName = <some string that decides the name>
Set TempRng = .Range(.Cells(A,B), .Cells(X,Y))
ActiveWorkbook.Names.Add Name:=rName, RefersTo:=Worksheets("Sheet1").TempRng

This gives me an "object or method not supported" error or something like that, on the Set TempRng line.

rName = <string ...>
Set TempRng = Worksheets("Sheet1").Range(Cells(A,B), Cells(X,Y))
ActiveWorkbook.Names.Add Name:=rName, RefersTo:= <blah blah...>

This gives me an "application defined or object defined error" on the same line.

Can you please help me find the correct way to define this range?

Upvotes: 4

Views: 19475

Answers (3)

Anil Sharaf
Anil Sharaf

Reputation: 3

Set TempRng = .Range(.Cells(A,B), .Cells(X,Y))

These refered cells should not contain a name that has spaces or dots or should not exceed 255 characters.

A name should not be Sn From To but it should be Sn_From_To

Upvotes: 0

Simon
Simon

Reputation: 577

Use: 

Refersto:="=" & TempRng.Address

Or more directly (as L42 says)

TempRng.Name = stName

To set your range make sure you've included "With" before you start "."ing things

With wsWhatever
    Set TempRng = .Range(.Cells(A,B), .Cells(X,Y))
end with

Upvotes: 1

L42
L42

Reputation: 19727

I think this is a bit more direct.

TempRng.Name = rname

Or you can even directly assign the name without using variable.

TempRng.Name = "myrangename"

The scope of the name is Workbook just in case that is critical in your needs. HTH.

Upvotes: 3

Related Questions