PDA

View Full Version : Storing range object temporarily in code not possible?



wpanssi
10-31-2010, 12:11 PM
I am looping through some cells. I want to save the formats and values temporarily into some variable but I can't seem to do it. If I uncomment this line:
d.value = c.value
I get error: Object variable or with block variable not set.

I don't want to store the cells in excel sheet but just temporarily in my code. Can I achieve this??

Here's my code, I also attached a sample fiel with the code and some data.

Sub macro()
Dim c As Range
Set c = ActiveCell
Dim d As Range

Do While (IsEmpty(c) = False)
' Why I can't do this?
'd.Value = c.Value
MsgBox c.Value
Set c = c.Offset(0, 1)
Loop
End Sub

mdmackillop
10-31-2010, 01:12 PM
Your sample is not clear as to how the saved value is used. You can use a Global Variable somthing like this

Option Explicit

Dim d As Range

Sub macro1()
Dim c As Range
Set c = ActiveCell
Set d = c
End Sub


Sub macro2()
MsgBox d.Value
End Sub

jaylotheman
10-31-2010, 05:03 PM
Hmm...

mikerickson
11-01-2010, 12:23 AM
Are you trying to store the values and formulas as an array
Dim c As Range
Dim imageOfC As Variant

Set c = Range("A1:B2")

imageOfC = c.Formula
MsgBox imageOfC(2, 1) & ", " & imageOfC(2, 2): Rem 2, =A2+1

wpanssi
11-05-2010, 11:53 AM
Hmmm.. Let me try to refrase the question:

If I have declared:
dim c as Range
Can I use
c.value = 10
if c has not been set to refer to any existing cell in worksheet? Just these two lines produce error, but is there a way I can create instances of the Range class without instances pointing to some specific cells?

GTO
11-05-2010, 12:01 PM
Maybe I'll get in over my head here, but here goes nuthin...

A Range is an Object, thus, while you have declared or dimensioned the variable's type as Range, it does not refer to anything, until you Set a reference to a range.

So... you would be attempting to set the value of a property (in this case the .Value property), wherein the property belongs to an Object that does not yet exist.