Rotate Values of Four Excel Cells using Macro

[pagination1]

Hi all,

I would be grateful for your help. The below is an extension of the often asked question "how do I create a macro which automatically swaps the value of two selected cells, when the macro is run".

I set out the situation below, for ease of reference:

1) A1 is a number between 1 and 3. Lets say this, for this example, the number is typed as "1".
2) A2 is a number between 1 and 3. Lets say this, for this example, the number is typed as "2".
3) A3 is a number between 1 and 3. Lets say this, for this example, the number is typed as "3".
4) A4 is a formula, which is "=10-(A1+A2+A3)", which, using this example is 4.
5) These cell addresses are fixed and will never change - if it is possible to create a macro that doesnt require me holding down ctrl and actually selecting A1,A2,A3 and A4, that would be perfect?

When I click the macro button, I want cells A1:A4 to rotate, so that:

A1=4
A2=1
A3=2
A4=3

Then, when I click the macro button again, the result will be:


A1=3
A2=4
A3=1
A4=2

Etc - you get the idea, all four cells rotate/swap as per the above, everytime the macro is run.

However, in order for this to work as I would like, when cell A4 is swapped to A1, I want the value of A4 only to be posted into A1. Not the formula.

Thus I want cell A4 to consistently use the formula ("=10-(A1+A2+A3)", no matter how many times I run the macro.

If this is possible, I would be extremely grateful for your help!

Thanks guys!

[pagination1]

And, for ease, below is the macro formula that rotates 4 numbers as set out above, but:

1)There is no formula provided within this code, as I would like to be included

2) I have to specifically select the 4 cells that I would like to rotate. I would like this to happen automatically, as the 4 cells to be rotated are always fixed.

Many thanks!

Sub SwapSelections()
Dim rCell1 As Range
Dim rCell2 As Range
Dim rCell3 As Range
Dim rCell4 As Range
Dim strg1 As String, strg2 As String, strg3 As String, strg4 As String

If Selection.Areas.Count > 1 Then
Set rCell1 = Selection.Areas(1).Cells(1, 1)
Set rCell2 = Selection.Areas(2).Cells(1, 1)
Set rCell3 = Selection.Areas(3).Cells(1, 1)
Set rCell4 = Selection.Areas(4).Cells(1, 1)

ElseIf Selection.Rows.Count > Selection.Columns.Count Then
Set rCell1 = Selection.Range("A1")
Set rCell2 = Selection.Range("A2")
Set rCell3 = Selection.Range("A3")
Set rCell4 = Selection.Range("A4")
Else
Set rCell1 = Selection.Range("A1")
Set rCell2 = Selection.Range("B1")
Set rCell3 = Selection.Range("C1")
Set rCell4 = Selection.Range("D1")
End If


strg1 = rCell1
strg2 = rCell2
strg3 = rCell3
strg4 = rCell4
rCell1 = strg4
rCell2 = strg1
rCell3 = strg2
rCell4 = strg3

End Sub

[Andy Pope]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("A1:A3")
    vntValue = ActiveSheet.Range("A4").Value
    For lngIndex = 3 To 2 Step -1
        rngSwap.Cells(lngIndex) = rngSwap.Cells(lngIndex - 1)
    Next
    rngSwap.Cells(1) = vntValue
    
End Sub

[pagination1]

Hi Andy,

Many thanks for this. However, the macro changes every cell to the number 4, after it has been run 3 times.

I'd still be grateful for your help - Jasper's formula below is almost there, it just needs some tweaks as I have gratefully requested.

[pagination1]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("A1:A3")
    vntValue = ActiveSheet.Range("A4").Value
    For lngIndex = 3 To 2 Step -1
        rngSwap.Cells(lngIndex) = rngSwap.Cells(lngIndex - 1)
    Next
    rngSwap.Cells(1) = vntValue
    
End Sub

Hi Andy (and any other kind forum readers),

I hope you can help again - I also hope this is an easy question!

Are you able to amend this macro so that the numbers rotate upwards as follows?:

A1: 1
A2: 2
A3: 3
A4: 4

Run macro:

A1:2
A2:3
A3:4
A4:1

Run Macro:

A1:3
A2:4
A3:1
A4:2

Run Macro:

A1:4
A2:1
A3:2
A4:3

Run Macro:

A1:1
A2:2
A3:3
A4:4

Thank you so much for your help!

[JasperD]

Sub test()
x = Range("A1").Value
y = Range("A2").Value
If Range("A3").Value = 1 Then Range("A1").Value = 2
If Range("A3").Value = 2 Then Range("A1").Value = 3
If Range("A3").Value = 3 Then Range("A1").Value = 4
If Range("A3").Value = 4 Then Range("A1").Value = 1
Range("A3").Value = y
Range("A2").Value = x
End Sub

Please click the * below if this helps

[pagination1]

Hi Jasper,

Thanks very much for this - I think we are almost there, but below is the problem.

Assume we start with:

A1=1
A2=2
A3=3
A4=4

I run your macro, and the cells, read:

A1=4,
A2=1
A3=2
A4=4.

A4 is the problem. A4 has to always be the formula (=10-(A1+A2+A3)), thus, A4 should read as 3. But it does not, it still reads as 4.

Would you be kind enough to tweak this formula, if possible?

Many thanks,

Nick

[pagination1]

And you may be wondering why I need a formula (10-(A1+A2+A3) for A4, when a simple rotation of the 4 cells would have the same effect. This is because the numbers within A1, A2, A3 and A4 will actually (for the purposes of my excel chart) total 135, thus the formula I will use for cell A4 will be (135-(A1+A2+A3)).

I was therefore keeping my example simple, for the purposes of the above, and I should hopefully be able to amend the macro you provide!

[Andy Pope]

Not for it doesn't.

starting with 1,2,3,4
4,1,2,3
3,4,1,2
2,3,4,1

then back to
1,2,3,4

[pagination1]

I'm really not sure what the problem is then, Andy - thanks for responding. I have re copied and pasted your code, and, if starting with
1
2
3
4

It moves to

4
1
2
4

then

4
4
1
4

then


4
4
4
4


Am I doing something stupid?!

[Andy Pope]

You have calculation set to manual rather than automatic

[pagination1]

Thanks, Andy/Jasper,

Having read your extremely helpful answers, I have realised that I need to be exact with regards to how I need this to work, as your forumlas (whilst they work), will not translate into the specific requirements of my spread sheet.

1) A1 will be any number. (by way of background info only usually between 5 and 75, but not guaranteed)
2) A2 will be any number. (by way of background info only usually between 5 and 75, but not guaranteed)
3) A3 will be any number. (by way of background info only usually between 5 and 75, but not guaranteed)
4) A4 will be a formula (135- (A1+A2+A3).

Thus, using the below example, lets say we start with:

1) A1= 25
2) A2= 35
3) A3= 45
4) A4 = 30 (because of the formula 135- (A1+A2+A3).

When I run the macro, this should change to:

1) A1= 30
2) A2 = 25
3) A3= 35
4) A4= 45 (because of the formula 135- (A1+A2+A3).

And, when I run the macro again, the result would be:

1) A1= 45
2) A2 = 30
3) A3= 25
4) A4= 35 (because of the formula 135- (A1+A2+A3).

You can see that each of the 4 cells shift down in rotation, but with A4 consistently using the formula (and this formula is not moved to any other cell).

Would it be possible to write a macro that works as above?

Thank you so much.

[Andy Pope]

My same macro works with the new values, as long as calculation is automatic.

[pagination1]

Thanks Andy - your attachment says it is locked for editing. It also says the Macro is not available when I open a read only copy?

Thanks mate.

[pagination1]

This now works perfectly - Andy - thank you so much for your patience and your help - very much appreciated indeed.

Last question.

How do I get this to work for merged cells?

Ie, The first cell is actually cells A1+A2 merged.
The second cell is actually cells A3+A4 merged,
The third cell is cells A4+A5 merged; and
The fourth cell (with the formula) is cells A6+A7 merged.

Thanks mate - so nearly there!

[pagination1]

Attached is the excel spreadsheet demo I am working on.

You can see the exact layout on there. This might be easier!

[pagination1]

Correct version attached.

[pagination1]

Also Andy (and thank you for your patience with me),

Calculation is 100% set to automatic. Within the formula ribbon, I have gone to "calculation options" and have definitely selected "automatic".

Yet, I still end up with the situation of:

A1=4
A2=4
A3=4
A4=4

I am stumped?!

[pagination1]

Also Andy (and thank you for your patience with me),

Calculation is 100% set to automatic. Within the formula ribbon, I have gone to "calculation options" and have definitely selected "automatic".

Yet, I still end up with the situation of:

A1=4
A2=4
A3=4
A4=4

I am stumped?!

Ignore me please - the formula for A4, had been blimmin' deleted somewhere along the line. My apologies.

[Andy Pope]

Those are setting at you end. You may well get Proected view and Enable Macro prompts.
You will need to enable them to see the workbook function. But the code is the same as I originally posted.

[shyamhappy]

Please have a check on this.

Here I Swap the data in temporarily variables and then put in the cells

This is for 1,2,3, 4 and I have not removed the formula. Using formula it has made easier to code .

[Andy Pope]

Why not post your workbook example then.

[pagination1]

The formula in A10 is incorrect in the attached sheet - my bad but no worries. It should read =10-(A1+A4+A7)

[pagination1]

Hi Shyam,

Yes, your version works - thank you! But would you be able to create a version that works as per my attached book?

Here you will see that not only are cells merged, but there is a row gap between each merged cell.

If you could get that to work, that would be amazing!

[pagination1]

Also, Shyam, I can't see the code you used? Usually I can edit the Macro code (right click on the button, assign macro, edit the code) etc, but it seems to all be blank?

[shyamhappy]

Attachment 256058

Same code I have applied Please have a check

Here I Swap the data in temporarily variables and then put in the cells

This is for 1,2,3, 4 and I have not removed the formula. Using formula it has made easier to code .

[shyamhappy]

You can try in above attachment of changing code.

[pagination1]

Hi Shyam,

I appreciate your help, but I'm afraid that this doesn't work at all, and the numbers jump all over the place.

Andy Pope got very far with this - I attach the latest spreadsheet. On the left, highlighted in yellow, you can see Andy Pope's working macro code.

Cells A1, A2, A3 and A4 (with the formula inbedded in cell A4) rotate perfectly.


On the right, in green, you will see the layout that I need to also work. You can see that there are merged cells and a single row gap in between each merged cell. I need "button 2" to do what "button 1" does (but for the green layout.)

I feel that we are so nearly there - I would be so grateful for the final push to the finish line!

[Andy Pope]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K4,K7")
    vntValue = ActiveSheet.Range("K10")
    For lngIndex = 3 To 2 Step -1
        rngSwap.Areas(lngIndex).Cells(1) = rngSwap.Areas(lngIndex - 1).Cells(1)
    Next
    rngSwap.Cells(1) = vntValue
    
End Sub

[shyamhappy]

Attachment 256067

Here is the macro for your requirement.

________________________________________________________________________________
if you think this has helped please add to reputation.

[pagination1]

Guys, you are all legends, thank you very much for this. Andy - in particular - thanks for sticking with this.

I will have a play around now with this. I hope I don't have to trouble you again with more questions, but this is perfect for now!

Thanks to all!

[shyamhappy]

It was great experience with this thread

[pagination1]

And for those just reading this post, the above macro code rotates the numbers downwards:
1
2
3
4

4
1
2
3

3
4
1
2

etc, which is the wrong way around for my purposes!

[Andy Pope]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("A1:A4")
    vntValue = rngSwap.Cells(1)
    For lngIndex = 2 To 4
        rngSwap.Cells(lngIndex - 1) = rngSwap.Cells(lngIndex)
    Next
    rngSwap.Cells(4) = vntValue
    
End Sub

[pagination1]

Thanks, Andy,

Although what this macro does is delete the formula that is in A4

A1: 1
A2: 2
A3: 3
A4: 4 (This number is 4 because of the formula, within A4, which is (10-A1+A2+A3)

Would you be kind enough to amend the macro, so that A4 always contains this formula, and isn't deleted?

Thank you!

(were it not for the macro deleting the formula, the numbers do indeed rotate upwards).

[pagination1]

So, to clarify, your above macro (as I quote at the above post 32), works perfectly except the numbers rotate downwards, when I need them to move upwards. This macro preserves the formula that is contained within cell A4.

Thank you for your patience!

Nick

[Andy Pope]

You didn't mention formula in A4 so assumed it was just values.

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("A1:A4")
    vntValue = rngSwap.Cells(4)
    For lngIndex = 1 To 2
        rngSwap.Cells(lngIndex).Value = rngSwap.Cells(lngIndex + 1).Value
    Next
    rngSwap.Cells(3).Value = vntValue
End Sub

[pagination1]

Hi Andy - apologies for the omission - but thank you so much for this, it works!

Last question (really), and you had worked this out for me yesterday, but I would also like this macro to work for when cells are merged, and with a gap inbetween each cell. Would you mind looking at the attached sheet - you will see the yellow section is the macro you just gave me, which works perfectly - the formula remains in A4 and the numbers rotate upwards.

However, I also would be grateful for a macro which does the same thing, but for the green cells. You will see that the macro within "button 2", is the one you gave me yesterday, but the numbers rotate "downwards", when they should rotate "upwards".

Would you be able to provide this last code for me?

Thank you very much,

Nick

[pagination1]

Also, for clarity, there is a formula in K10 of the above referred chart, which needs to remain in that cell, after the macro is run.

Thank you!

[Andy Pope]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K4,K7,K10")
    vntValue = rngSwap.Areas(4).Cells(1).Value
    For lngIndex = 1 To 2
        rngSwap.Areas(lngIndex).Cells(1).Value = rngSwap.Areas(lngIndex + 1).Cells(1).Value
    Next
    rngSwap.Areas(3).Cells(1).Value = vntValue
    
End Sub

[pagination1]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K4,K7,K10")
    vntValue = rngSwap.Areas(4).Cells(1).Value
    For lngIndex = 1 To 2
        rngSwap.Areas(lngIndex).Cells(1).Value = rngSwap.Areas(lngIndex + 1).Cells(1).Value
    Next
    rngSwap.Areas(3).Cells(1).Value = vntValue
    
End Sub

Hi Andy,

I have another question. First, I hope you don't mind these continuous questions over the last couple of days. By way of background, I am working on a very ambitious spreadsheet (for me anyway), but I am a complete novice in relation to macro code, although I am learning as much as I can. The macros you have provided me with have already transformed the productivity of this spreadsheet.

So far, in all of the forums I've looked at, you're the only one who has been able to present me with macro code which works perfectly, so I am really grateful for your help, and you always manage to respond so quickly!

I have about 4 other macro codes I am working on, and then the spreadsheet will be complete. For now, however, in relation to the above code, would you be able to adapt this, so that it works as above, but with only 3 cells used:

FYI, the below cells are all merged cells.

K1: 1
K4: 2
K7: 7 (K7 always contains a forumla, which is (10-(K1+K4)

Run macro:

K1: 2
K4: 7
K7: 1 (because of the forumula)

As before, the numbers are all moving upwards.

Each time you provide me with a macro, I am learning more and more, particularly as I am studying the macros you gave me before, and how you have adapted them to work with my new queries.

Thank you again - but if these questions are becoming tedious for you, please do let me know.

Nick

[pagination1]

Andy, you are a legend. It works perfectly.

Thank you for taking the time to help me out with this - it is really so very much appreciated.

[pagination1]

And should this assist, attached is the worksheet with the set up, as referred to above (but the macro code does not work), and just deletes all of the numbers therein, including the formula in K7.

[Andy Pope]

simple take 1 off of everything.

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K5,K9")
    vntValue = rngSwap.Areas(3).Cells(1).Value
    For lngIndex = 1 To 1
        rngSwap.Areas(lngIndex).Cells(1).Value = rngSwap.Areas(lngIndex + 1).Cells(1).Value
    Next
    rngSwap.Areas(2).Cells(1).Value = vntValue
    
End Sub

And this should be flexible enough to handle multiple areas where the last always contains the formula. Just update the cell names.

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K5,K9")
    vntValue = rngSwap.Areas(rngSwap.Areas.Count).Cells(1).Value
    For lngIndex = 1 To rngSwap.Areas.Count - 2
        rngSwap.Areas(lngIndex).Cells(1).Value = rngSwap.Areas(lngIndex + 1).Cells(1).Value
    Next
    rngSwap.Areas(rngSwap.Areas.Count - 1).Cells(1).Value = vntValue
    
End Sub

[pagination1]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K5,K9")
    vntValue = rngSwap.Areas(rngSwap.Areas.Count).Cells(1).Value
    For lngIndex = 1 To rngSwap.Areas.Count - 2
        rngSwap.Areas(lngIndex).Cells(1).Value = rngSwap.Areas(lngIndex + 1).Cells(1).Value
    Next
    rngSwap.Areas(rngSwap.Areas.Count - 1).Cells(1).Value = vntValue
    
End Sub

This one is wonderful!

A quick question, how would I adapt this so that BOTH K9 contain a formula AND K5 also contains a formula? So when the number rotate upwards, but K9 and K5 retain their formula?

I think your answer to this one might solve all of my future macro requirements!

[Andy Pope]

Well that only requires

Sub Button1_Click()

    Range("K1").Value = Range("K5").Value
    
End Sub

[pagination1]

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K5,k9")
    Range("K1").Value = Range("K5").Value
    vntValue = rngSwap.Areas(rngSwap.Areas.Count).Cells(1).Value
    For lngIndex = 1 To rngSwap.Areas.Count - 2
        rngSwap.Areas(lngIndex).Cells(1).Value = rngSwap.Areas(lngIndex + 1).Cells(1).Value
    Next
    rngSwap.Areas(rngSwap.Areas.Count - 1).Cells(1).Value = vntValue

End Sub

I'm not sure where to include this addition - I tried it as above, but deletes the formula within K5 once the macro is run. (It keeps the formula within K9, however).

What am I doing wrong?

Many thanks,

Nick

[Andy Pope]

It is not an addition it is all the code needed. It replaces all that other code.

If K9 and K5 have formula all you can do us uplift K5 value in to K1.

[pagination1]

It is not an addition it is all the code needed. It replaces all that other code.

If K9 and K5 have formula all you can do us uplift K5 value in to K1.

Ok, I see - yes I think that makes sense for 3 cells – I will see if this works as intended in my spreadsheet.

In the meantime, may I ask what the macro code would be for 4 cells as follows (with the usual “numbers rotating upwards” as we have been discussing). The difference here, is that there is a formula within both K5 AND K9. Thus there are two changing variables each time the macro is run.

Starting Numbers

K1 = 1
K5 = 2 (K5 is a “2” because of the formula “= m5”. M5 contains a number which will change every time the macro is run, because of other formulas running within the spreadsheet. Let’s say that this is a 2 for now)
K7 = 3
K9 = 4 (because of a formula - "=10-K1+K5+K7"

Macro is run:

K1= 2
K5= 5 (this is now a five, because the value in m5 has changed to, let's say, a 5)
K7= 4
K9= -1 (this is -1 now, because K9 contains the formula "=10-(K1+K2+K7)"

Macro is run:

K1= 5
K5= 1 (this is now a 1, because the formula in m5 has changed to, let's say, a 1)
K7= -1
K9= 5 (this is a 5 now, because k9 contains the formula "=10-(K1+K2+K7)"

The above is essentially the crux of how my spreadsheet operates. I think your macro code for this would nail it!

[pagination1]

And just so you know - I'm not asking pointless questions here - each of the macros you have given me are being used now - and I am very grateful for your help. The further questions are for new macros that are needed!

[Andy Pope]

store the 2 values prior to M5 update. The write the values to the static cells.

Sub Button1_Click()

    Dim vntValue As Variant
    Dim vntValue2 As Variant
    
    vntValue = Range("K5").Value
    vntValue2 = Range("K9").Value
    
    Range("K1").Value = vntValue
    Range("K7").Value = vntValue2
    
End Sub

[pagination1]

Hi Andy,

Thank you, but this doesn't rotate the numbers?

You know the below code you gave me, is it possible to amend this so that K5 is also a formula (and this formula needs to stay in K5 when the macro is run). K13 is also a formula (but this macro already keeps this formula in K13, so you don't need to worry about that)

Sub Button1_Click()

    Dim rngSwap As Range
    Dim lngIndex As Long
    Dim vntValue As Variant
    
    Set rngSwap = ActiveSheet.Range("K1,K5,K9,K13")
    vntValue = rngSwap.Areas(4).Cells(1).Value
    For lngIndex = 1 To 2
        rngSwap.Areas(lngIndex).Cells(1).Value = rngSwap.Areas(lngIndex + 1).Cells(1).Value
    Next
    rngSwap.Areas(3).Cells(1).Value = vntValue
    
End Sub

If the above code could be amended to allow for K5 as also containing a formula (like K13), I am convinced that this is all I need and will work in my spreadsheet.

Thanks, again, so very much.

[Andy Pope]

There must be more to your problem as that code simply lift the 2 formula values in K5 and K9 up into K1 and K7, which is what you specified.

Sub Button1_Click()

    Dim vntValue As Variant
    Dim vntValue2 As Variant
    
    vntValue = Range("K5").Value
    vntValue2 = Range("K13").Value
    
    Range("K1").Value = vntValue
    Range("K9").Value = vntValue2
    
End Sub

It would change to this if K5 and K13 have formula.

[pagination1]

Yes, you are right, there is more to the problem than this. I'm just trying to figure out what is going wrong (nothing wrong with your macro, just how my spreadsheet is working).

I'll get back to you once I've figured out what is happening at my end.

Cheers,

[pagination1]

Hi Andy,

Ok, this is the actual problem within my spreadsheet. The below sets out exactly how I would like it to operate.

E5: I type in any value I like here. There is no formula within this cell.

E7: The value required here, once the macro is run, is contained within Z32. There is NO formula within this cell. Can the macro therefore refer to cell Z32 as being the value that must be in E7, without the requirement of a formula actually being typed within E7? This is because I will sometimes need to type a specific value in this cell, which will of course delete the formula. However, once I run the macro, it will reinstate the value of z32 into E7.

E9: The value required here once the macro is run is contained with z31. There is no formula within this cell. As above, can the macro refer to cell Z32 as being the required value for cell E9?

E11: Is a formula, which is contained within this cell. (which will be, 135 – (E5+E7+E9). This formula must remain in this cell. I will never need to amend this.

As before, the values in each of these cells need to rotate “upwards”, once the macro is run.

Can you help?

Thanks so much!

[pagination1]

And, to be clear, should this make a difference, all of the above 4 referred cells comprise of two merged cells each. There is no blank row inbetween each of the merged cells.

[Andy Pope]

That does not make sense. the only thing changing would be E5 and only that would happen once.
If E7 gets it's value from Z31 and E9 from Z32 what is changing?

[pagination1]

Thus everytime the macro is run, it will actually input fresh data from z31 into E7 and from z32 into E9.

[pagination1]

Each time the macro is run, as a consequence of the new data which is within e5, e7, e9 and e11, the values of z31 and z32 change.

Does that resolve the confusion?

It is a very complicated spreadsheet!

[Andy Pope]

Sub X()

    Dim rngData(3) As Range
    Dim vntStaticData(2) As Variant
    
    Set rngData(1) = Range("E5")
    Set rngData(2) = Range("E7")
    Set rngData(3) = Range("E9")
    
    vntStaticData(1) = Range("Z31").Value
    vntStaticData(2) = Range("Z32").Value
    
    rngData(1).Value = rngData(2).Value
    rngData(2).Value = vntStaticData(1)
    rngData(3).Value = vntStaticData(2)
    
End Sub

[pagination1]

Sub X()

    Dim rngData(3) As Range
    Dim vntStaticData(2) As Variant
    
    Set rngData(1) = Range("E5")
    Set rngData(2) = Range("E7")
    Set rngData(3) = Range("E9")
    
    vntStaticData(1) = Range("Z31").Value
    vntStaticData(2) = Range("Z32").Value
    
    rngData(1).Value = rngData(2).Value
    rngData(2).Value = vntStaticData(1)
    rngData(3).Value = vntStaticData(2)
    
End Sub

Hi Andy,

I hope you had a good weekend. I would be grateful for your assistance on the below! This is very similar to your above macro code, but the exception this time is that cell E5 needs to refer to a another cell (z34) when the macro is run (whereas before, your code did not need E5 to refer to another cell).

E5: The value required here, once the macro is run is at z34. There is no formula within this cell.

E7: The value required here, once the macro is run, is contained within Z35. There is no formula within this cell.

E9: The value required here once the macro is run is contained with z36. There is no formula within this cell.

E11: Is a formula, which is contained within this cell. (which will be, 135 – (E5+E7+E9). This formula must remain in this cell. I will never need to amend this.

As before, once the macro is run, the numbers need to rotate upwards.

I think this is all just a simple tweak to your above code, but I can't figure it out.

Thank you once again!

Nick

[pagination1]

This is wonderful and is now being used in my spreadsheet! I nearly have all the macros I need.

Thank you so much - I will play around with this macro and see if I can get it to fit with my other requirements.

I can't tell you how much help you've been.

[Andy Pope]

The numbers can not rotate upwards as their value is coming from a predefined location.

Sub X()

    Dim rngData(3) As Range
    Dim vntStaticData(2) As Variant
    
    Set rngData(1) = Range("E5")
    Set rngData(2) = Range("E7")
    Set rngData(3) = Range("E9")
    
    vntStaticData(1) = Range("Z34").Value
    vntStaticData(2) = Range("Z35").Value
    vntStaticData(3) = Range("Z36").Value
    
    rngData(1).Value = vntStaticData(1)
    rngData(2).Value = vntStaticData(2)
    rngData(3).Value = vntStaticData(3)
    
End Sub

[pagination1]

Thanks Andy - and yes of course, they cannot rotate - that makes sense, and thus I don't need a "rotation" to happen.

However, when I run your code, I get an excel error message saying "subscript out of range". When I debug, it refers to the vntStaticData(3) = Range("Z36").Value line as having a problem - but I can't see why that would be.

Do you have any ideas?

[pagination1]

It's ok - I figured it out - slowly I am learning this!

The issue was this line Dim vntStaticData(2) As Variant

The "2" needed to be a "3".

[Andy Pope]

Increase array size

    Dim vntStaticData(3) As Variant

[pagination1]

Hi Andy,

I think I am nearly there now – I have used 7 of your provided macro codes so far, and they have been so incredibly useful!

I actually think I only need about 2 more, and then my spreadsheet is complete!

Could you possibly provide a code for the below?

When the macro is run:

1) The contents of B3 need to be copied to A23.
2) The contents of B4 need to be copied to A24
3) The contents of A20 need to be copied to B27
4) The contents of A21 need to be copied to B28

I can’t use formulas within excel for various reasons, so need to rely on a macro.

Can you help (once again!).

Thanks so much,

Nick

[pagination1]

Actually, I figured this one out myself, by reviewing the information you have given me previously.

Sub Button1_Click()

Range("a23").Value = Range("b3").Value
Range("a24").Value = Range("b4").Value
Range("b27").Value = Range("a20").Value
Range("b28").Value = Range("a21").Value
 
End Sub

[Andy Pope]

Glad you managed to resolve it yourself

[pagination1]

The idea is that these codes have to be programmed to just one VBA button.

[pagination1]

Glad you managed to resolve it yourself

Hi Andy,

I hope you don't mind me cross linking a thread, but I didn't know how to contact you. You have nailed every single macro request I've ever had - would you be able to look at this thread, if you have the time, and see if you can find a solution?

http://www.excelforum.com/excel-prog...another-2.html

Thank you so much.

[pagination1]

Hi Andy,

Another macro code question for you!

Is it possible for a macro to run a particular code depending on the value of a cell?

For example, if cell A14=1, then I would like the below macro to run:

Sub X()

    Dim rngData(2) As Range
    Dim vntStaticData(2) As Variant
    
    Set rngData(1) = Range("b3")
    Set rngData(2) = Range("b4")
    
    vntStaticData(1) = Range("r3").Value
    vntStaticData(2) = Range("r4").Value

    rngData(1).Value = vntStaticData(1)
    rngData(2).Value = vntStaticData(2)
   

Range("a23").Value = Range("b3").Value
Range("a24").Value = Range("b4").Value
Range("b27").Value = Range("a20").Value
Range("b28").Value = Range("a21").Value
 
End Sub

However, if cell A14=2, I would like the below macro to run:

Sub X()

    Dim rngData(2) As Range
    Dim vntStaticData(2) As Variant
    
    Set rngData(1) = Range("d3")
    Set rngData(2) = Range("d4")
    
    vntStaticData(1) = Range("r14").Value
    vntStaticData(2) = Range("r15").Value

    rngData(1).Value = vntStaticData(1)
    rngData(2).Value = vntStaticData(2)
   
Range("a23").Value = Range("d3").Value
Range("a24").Value = Range("d4").Value
Range("b27").Value = Range("c20").Value
Range("b28").Value = Range("c21").Value

End Sub

Is this possible?

Thank you!

[Andy Pope]

Give those X routines different names and then control which one to execute in another routine linked to the button.

Sub Macro_X1()

    Dim rngData(2) As Range
    Dim vntStaticData(2) As Variant
    
    Set rngData(1) = Range("b3")
    Set rngData(2) = Range("b4")
    
    vntStaticData(1) = Range("r3").Value
    vntStaticData(2) = Range("r4").Value

    rngData(1).Value = vntStaticData(1)
    rngData(2).Value = vntStaticData(2)
   

Range("a23").Value = Range("b3").Value
Range("a24").Value = Range("b4").Value
Range("b27").Value = Range("a20").Value
Range("b28").Value = Range("a21").Value
 
End Sub
Sub Macro_X2()

    Dim rngData(2) As Range
    Dim vntStaticData(2) As Variant
    
    Set rngData(1) = Range("d3")
    Set rngData(2) = Range("d4")
    
    vntStaticData(1) = Range("r14").Value
    vntStaticData(2) = Range("r15").Value

    rngData(1).Value = vntStaticData(1)
    rngData(2).Value = vntStaticData(2)
   
Range("a23").Value = Range("d3").Value
Range("a24").Value = Range("d4").Value
Range("b27").Value = Range("c20").Value
Range("b28").Value = Range("c21").Value

End Sub
Sub X()
    
    Select Case Range("A14").Value
    Case 1
        Macro_X1
    Case 2
        Macro_X2
    Case Else
        MsgBox "Invalid value"
    End Select
    
End Sub

[pagination1]

Wonderful - thank you very much - and thank you for your super quick response!