What would be the correct data type if I wanted to declare
and set the variable below?

dim StartPoint as ???
dim myRange as ???

Set StartPoint = Range("A1").Select
Set myRange = ActiveCell.Offset(rowindex:=0,
columnindex:=0).select
I know I can always use 'As Variant' but this uses the
most memory. Is there something else I should be using?

Jeff

Hi
dim StartPoint as range
dim myRange as range

Set StartPoint = Range("A1")
Set myRange = ActiveCell


--
Regards
Frank Kabel
Frankfurt, Germany


Jeff Armstrong wrote:
What would be the correct data type if I wanted to declare
and set the variable below?

dim StartPoint as ???
dim myRange as ???

Set StartPoint = Range("A1").Select
Set myRange = ActiveCell.Offset(rowindex:=0,
columnindex:=0).select
I know I can always use 'As Variant' but this uses the
most memory. Is there something else I should be using?

Jeff

Ok, here's the problem. I have another variable that I
declared as:

Dim StartRange As Range

I want to store a cells address to this variable. In the
past I delcared the variable as a variant type and then
used the line of code:

StartRange = ActiveCell.Address

I am trying to optimize my code. Now when I try to do it
with the Range type I get the following error:

Object variable or With block variable not set

How do I need to declare the variable and then store a
particular cell address to that variable?


-----Original Message-----
Hi
dim StartPoint as range
dim myRange as range

Set StartPoint = Range("A1")
Set myRange = ActiveCell


--
Regards
Frank Kabel
Frankfurt, Germany


Jeff Armstrong wrote:
What would be the correct data type if I wanted to
declare
and set the variable below?

dim StartPoint as ???
dim myRange as ???

Set StartPoint = Range("A1").Select
Set myRange = ActiveCell.Offset(rowindex:=0,
columnindex:=0).select
I know I can always use 'As Variant' but this uses the
most memory. Is there something else I should be using?

Jeff
.

a cell address is a string, a reference to the cell is a range

------- using range reference --------
Dim rng as range

set rng = ActiveCell

Usage:
rng.Value = 6

------ using address property ------

Dim sAddr as String
sAddr = ActiveCell.Address

Usage:
Range(sAddr).Value = 6

--
Regards,
Tom Ogilvy

"Jeff Armstrong" wrote in message
...
Ok, here's the problem. I have another variable that I
declared as:

Dim StartRange As Range

I want to store a cells address to this variable. In the
past I delcared the variable as a variant type and then
used the line of code:

StartRange = ActiveCell.Address

I am trying to optimize my code. Now when I try to do it
with the Range type I get the following error:

Object variable or With block variable not set

How do I need to declare the variable and then store a
particular cell address to that variable?


-----Original Message-----
Hi
dim StartPoint as range
dim myRange as range

Set StartPoint = Range("A1")
Set myRange = ActiveCell


--
Regards
Frank Kabel
Frankfurt, Germany


Jeff Armstrong wrote:
What would be the correct data type if I wanted to
declare
and set the variable below?

dim StartPoint as ???
dim myRange as ???

Set StartPoint = Range("A1").Select
Set myRange = ActiveCell.Offset(rowindex:=0,
columnindex:=0).select
I know I can always use 'As Variant' but this uses the
most memory. Is there something else I should be using?

Jeff
.

Dim StartRange As Range
Set StartRange = ActiveCell
MsgBox StartRange.Address

HTH. Best wishes Harald

"Jeff Armstrong" skrev i melding
...
Ok, here's the problem. I have another variable that I
declared as:

Dim StartRange As Range

I want to store a cells address to this variable. In the
past I delcared the variable as a variant type and then
used the line of code:

StartRange = ActiveCell.Address

I am trying to optimize my code. Now when I try to do it
with the Range type I get the following error:

Object variable or With block variable not set

How do I need to declare the variable and then store a
particular cell address to that variable?


-----Original Message-----
Hi
dim StartPoint as range
dim myRange as range

Set StartPoint = Range("A1")
Set myRange = ActiveCell


--
Regards
Frank Kabel
Frankfurt, Germany


Jeff Armstrong wrote:
What would be the correct data type if I wanted to
declare
and set the variable below?

dim StartPoint as ???
dim myRange as ???

Set StartPoint = Range("A1").Select
Set myRange = ActiveCell.Offset(rowindex:=0,
columnindex:=0).select
I know I can always use 'As Variant' but this uses the
most memory. Is there something else I should be using?

Jeff
.