Set range and define named range in VBA

Question

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?

L42 · Accepted Answer · 2015-02-12 23:53:39Z

3

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.

answered Feb 12, 2015 at 23:53

L42

19.8k11 gold badges47 silver badges70 bronze badges

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

3 Comments

Jabberwocky Over a year ago

I don't understand. I don't think there is any problem with assigning a name. In both cases, as I debug, I get an error on the Set TempRng line.

Simon Over a year ago

yeah sorry, i miss-read your post (where you clearly said it was the "set" lines that were failing). See my edited/revised answer above :)

L42 Over a year ago

@Jabberwocky Check the values of A, B, X and Y first. Your syntax is correct but make sure you have the correct values for all variables used. If one returns error or a string (for A and X) at least, it will fail. Once you've check that all values are valid values, I don't see any reason for your code to fail. Except with what Simon posted regarding the use of With Statement.

Simon · Accepted Answer · 2015-02-13 00:38:28Z

1

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

edited Feb 13, 2015 at 0:38

answered Feb 12, 2015 at 23:46

Simon

5776 silver badges16 bronze badges

3 Comments

Jabberwocky Over a year ago

I will try that once I get past the Set TempRng line. Can you tell me how I should set the range itself (I mean the extent)?

Simon Over a year ago

Oh ok, try explicit cell references like you did above i.e. Set TempRng = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(A,B), Worksheets("Sheet1").Cells(X,Y)) Or just: With Worksheets("Sheet1"). set TempRng = .Range(.Cells(a,b),.Cells(b,c)) Maybe you left out the "With" in the top one, and left out the Worksheet object for the second one?

Davesexcel Over a year ago

If you use actual numbers in place of the variables, and it works, then it's the variables giving you issues.

subdeveloper · Accepted Answer · 2024-04-15 10:32:25Z

0

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

edited Apr 15, 2024 at 10:32

subdeveloper

2,69416 silver badges26 bronze badges

answered Apr 13, 2024 at 1:19

Anil Sharaf

32 bronze badges

Collectives™ on Stack Overflow

Set range and define named range in VBA

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related