Geek Answers Handbook

Save range to variable

I wrote some functional VBA:

Sheets("Src").Range("A2:A9").Copy Destination:=Sheets("Dest").Range("A2")

I want to extract the source range into a variable for flexibility.

SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")

However, this does not work. Which SrcRange should be darkened? Is the first line even correct?
I tried Dimming SrcRange as Range and it gave me
Runtime error 91: Object Variable or With block variable not set

I am not very familiar with the language, and the documentation left me wanting (I could not find the return type for calling the Tables (index), this was the closest I found). When I click Record Macro, I perform some actions and stop, the macro body remains empty.

Can anyone shed some light on how to use SrcRange as a variable?

+5
source share
3 answers

:

Dim SrcRange As Range ' you should always declare things explicitly
Set SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")

"" , .

:

Dim Src As Variant
Src= Sheets("Src").Range("A2:A9").Value 'Read range to array
'Here you can add code to manipulate your Src array
'...
Sheets("Dest").Range("A2").Value = Src 'Write array back to another range
+9

, , - .

, / FROM Range("A2:A9") INTO Variant Array vArray ( Variant Array Sheet, Src):

vArray = Sheets("Src").Range("A2:A9").Value

Range (SrcRange) ADDRESS Sheets("Src").Range("A2:A9"):

Set SrcRange = Sheets("Src").Range("A2:A9")

, , , . , , , .

, , " " ( ) . , , , ( ), , . , .

+8

... :

Set SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")

Set . .

+7

More articles:


All Articles