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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top