What data type for Variable?

J

Jeff Armstrong

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
 
F

Frank Kabel

Hi
dim StartPoint as range
dim myRange as range

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

Jeff Armstrong

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?
 
T

Tom Ogilvy

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
 
H

Harald Staff

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

HTH. Best wishes Harald
 
Top