how do you name a range?

[John]

I'm very new. How do you name a range? Like give a cell or range a name
rather than use "a1" cells(1,1). All I could find was this:

ActiveWorkbook.Names.Add Name:="SecondTry", RefersToR1C1:= _
"=Sheet1!R8C2:R11C3"

I got this by using record macro. I don't understand it.

thanks

John

 

[RB Smissaert]

Range(Cells(1), Cells(4, 4)).Name = "NamedRange"

RBS

 

[Guest]

I guess you don't understand the R1C1 format. R is the row and C is the
column. The name this is refering to is the worksheet names.

The name of the range is "SecondTry"
the range is Sheet1!B8:C11


You can also name a range in VBA as follows

set MyRange = Range("A3:C7")

or

set MyRange = Range(cells(1,1), cells(5,5))

 

[Guest]

Define what you mean by name a range and how you would use the name.

 

[Alan Beban]

You can name a range on the worksheet by highlighting the range to be
named (e.g., A110), clicking on the down-arrow just above Column A,
typing the name (e.g., myRange), and entering. You could then refer to
the range in VBA as Range("myRange"); i.e., Range("myRange").Address
would return $A$1:$D$10.

You can also name a range in VBA with something like
Range("A110").Name = "myRange"; you could also refer to this range in
VBA as Range("myRange").

You can also set an Object Variable to refer to the range; e.g.,
Set myRange = Range("A110"). You could refer to this range in VBA with
myRange; i.e., myRange.Address would return $A$1:$D$10.

Alan Beban

 

[John]

I'm writing a thing that solves soduku. At some point you have to guess
and try it. I want keep track of the cells I'm guessing. I wanted an
array that holds all the guesses. I had CellTry() but what do you dim
that if each one is going to be a named cell? I couldn't get it to work
and gave up. It's easy enough just dim it CellTry(x,y) and save both
coordinates. It would have been cooler to save it as one name though
like try1, try2, try3 etc.

John

 

[Guest]

try1 = Cells(i,j).Address(0,0)

Try1 will hold something like "B9"

 

[Guest]

John: I wrote a program that solves Sudoku puzzles a couple of years ago. I
used a recursive program that just tried every possible combination until the
solution was found. It runs in about 5 seconds.

Don't try to solve the puzzle the same way you would manually solve the
puzzle, it is too complicated.

 

[John]

Interesting... i did exactly the same thing. Then I wanted to write a
real one, mostly to pratice using the vb/excel language which is new to me.

john

 

[John]

cool... what does the Address (0,0) mean?
John

 

[Guest]

I did my code using a 9 x 9 array. I copied the cells A1:I9 into the arrray
at the beginning of the program. Then output the results back into
spreadsheet at the end of the programming. I added cell formating to the
program by adding a border around the cells, change the column width, changed
the row width. I also made the color of the text different for the number
given and the number solved. the rest of the code was simple basic language
features.

 

[Dave Peterson]

..address() has some parms that you can pass to it:

..address(rowabsolute:=false,columnabsolute:=false)
or
..address(rowabsolute:=0,columnabsolute:=0)
or
..address(0,0)

All do the same thing--they stop the corresponding $ signs from showing up in
the address:

$B$9
or
$B9
or
B$9
or
B9

(depending on which parms are true or false)