Rotate Values of Four Excel Cells using Macro

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!

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!

Please Login or Register  to view this content.

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

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.

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?!

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.

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?!

Originally Posted by 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.

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!

Attached is the excel spreadsheet demo I am working on.

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

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

Correct version attached.

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 .

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!

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?

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 .

You can try in above attachment of changing code.

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!

Attachment 256067

Here is the macro for your requirement.

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

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!

It was great experience with this thread

Originally Posted by Andy Pope

Please Login or Register  to view this content.

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!

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!

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

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

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, 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.

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.

Please Login or Register  to view this content.

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!

Please Login or Register  to view this content.

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

Originally Posted by 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.

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!

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!

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)

Please Login or Register  to view this content.

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.

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,

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!

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.

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!

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

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.

Originally Posted by Andy Pope

Please Login or Register  to view this content.

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

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?

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".

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

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Is this possible?

Thank you!

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

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

Originally Posted by Andy Pope

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.