VBA: Simple Array

mqarep

Board Regular
Joined
Mar 12, 2002
Messages
91
I have a worksheet where a user clicks on a button and a macro runs which adjust a 12 month range of data to incorporate the new month. I have an undo button that returns the month range and associated data back to its state before the adjust range button was first clicked.

What I want to do is to accomplish this using an array.
This is what would need to happen:

Macro button 1:
Cells c20 through c34 need to be read into an array.

Macro button 2:
This array then needs to be read and the contents restored to cells c20 through c34.

How can I accomplish this?

Thanks.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

If you use a variant variable declared in the declarations section of your module, its value will persist over multiple macro calls. Hence:

Code:
Dim myVariant As Variant
Sub Macro_Button_1()
myVariant = Range("C20:C34")   'reads the array in
....processing...
End Sub
Sub Second_Button()
Range("C20:C34") = myVariant 'paste variant values back in
End Sub

Make sense?

Richard
 

mqarep

Board Regular
Joined
Mar 12, 2002
Messages
91
Thank you; however, the VBA code crashes at the point when it is to paste the values back in the range:
Range("C20:m34") = myVariant

I tested it by running the first macro to copy the cell range into the array, and then immediately ran the second macro to paste the contents of the array back into the cell range.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
mgarep

try change the Dim statement to

Private myVariant() As Variant
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi Mqarep

Did Jindon's suggestion work for you? Are you using the same size range to paste back into (that you have initialized the variable from)? My original code does work for me.

Richard
 

mqarep

Board Regular
Joined
Mar 12, 2002
Messages
91
The original error was caused because I failed to unprotect the sheet before the paste.
BUT, I still have a problem in that the array, when read to paste back in the values, is pasting back all blanks for the selected range. So either it is not copying the range correctly or something is wiping it out prior to the "paste."

BTW "Private myVariant() As Variant" doesn't work at all.

Thank you for your time and assistance.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Try declaring myVariant this way:

Code:
Public myVariant As Variant

The way I did it originally, both sets of code need to be in the same module, whereas with the Public statement they don't.
 

mqarep

Board Regular
Joined
Mar 12, 2002
Messages
91
This is module one (macro button):

Sub adjustrange()
'
' adjustrange Macro
'
ActiveSheet.Unprotect Password:="sqe123"
Dim myVariant As Variant
myVariant = Range("C20:N34")

Range("D20:N34").Select
Range("D20:N34").Cut Destination:=Range("C20:M34")
ActiveSheet.Unprotect Password:="sqe123"
Range("M20").Select
Selection.AutoFill Destination:=Range("M20:N20"), Type:=xlFillDefault
Range("M20:N20").Select

ActiveSheet.Protect Password:="sqe123"
End Sub
------------------------------------------------------------------------------------
This is module two (macro button):

Sub restorerange()
ActiveSheet.Unprotect Password:="sqe123"
Range("C20:N34") = myVariant 'paste variant values back in
ActiveSheet.Protect Password:="sqe123"
End Sub

------------------------------------------------------------------------------------
Where do I insert "Public myVariant As Variant" and what code, if any, does it replace?

Thanks. (I thought this would be so simple...lol)
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
What is this? A homework assignment?

If so, you may want to look at using a real array not fudging things with a variant.

If it is not HW, why do anything at all? The contents of C20:C34 are already in C20:C34!

Come to think of it, even if it is HW, why do anything at all? Just point the professor to this discussion and I'll be happy to tell her/him what I think of stupid assignments. Oops, I just did. {vbg}

I have a worksheet where a user clicks on a button and a macro runs which adjust a 12 month range of data to incorporate the new month. I have an undo button that returns the month range and associated data back to its state before the adjust range button was first clicked.

What I want to do is to accomplish this using an array.
This is what would need to happen:

Macro button 1:
Cells c20 through c34 need to be read into an array.

Macro button 2:
This array then needs to be read and the contents restored to cells c20 through c34.

How can I accomplish this?

Thanks.
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top