named cells

[John]

Is there a way to save and refer to a cell with a one name variable?
Instead of range something or cells(1,2) or "A1" can you just call it,
like, "ThatCell". Like... ThatCell = Cells(1,2) and then ThatCell means
Cell(1,2) not the value that is in cell(1,2).


I am doing a trial and error solution and have to save the cells I've
tried... not their value. I am hoping to save them in an array. I know I
can save them in an array Saved(x,y) with the colum,row coordinates...
it's just kind of a pain.

I'd like to end up with trial(1) trial(2) etc .

thanks

John

 

[Gary Keramidas]

just set it to a range, but i'm not sure what you mean by "save". save for how
long?
couple examples:

Sub test()
Dim ThatCell As Range
Set ThatCell = Worksheets("Sheet1").Range("A1")
ThatCell = 1
End Sub


Sub test2()
Dim i As Long
Dim ThatCell(5) As Range
For i = 1 To 5
Set ThatCell(i) = Worksheets("sheet1").Cells(i, 1)
ThatCell(i) = i + 1
Next
End Sub

 

[Guest]

Dim ThatCell As Range
Set ThatCell = Cells(1, 2)

Of course, ThatCell does not have to refer to only one cell (it can be
multiple areas/cells - it just cannot span different worksheets). You can
add cells to it by

Set ThatCell = Union(ThatCell, Cells(1, 3))

and check if a cell is a part of ThatCell range by

If Intersect(ThatCell, Cells(1, 4)) Is Nothing Then
'Not part of the range
Else
'Included in ThatCell range
End If