Shifting values to adjacent cells when F9 key is pressed

[PeterH]

Does anyone know a way of moving a cell value to the adjacent cell when F9 is pressed to recalculate?

For example if a number/value appears in cell A1 due to pressing F9 to recalculate the spreadsheet, then the next time the F9 key is pressed the value in A1 moves to the right (cell B1) and a new value appears in A1 due to recalculation.

The reason I want to do this is to record new values but retain the previous results for comparison over say ten or twenty recalculations.

I would appreciate any ideas as I appear to be too dense to work it out myself so far.

Thank you in advance,

PeterH

 

[Luke M]

It's "possible" with some VB programming, but I wouldn't recommend it. A better way would be to just have a table/list and add the new values to the end. This keeps your data from shifting around. If you have formulas that need to refer to "latest" info, you should build those formulas to dynamically pull last data entry from list, and not rely on always being in the same cell.

 

[anupamtiwari05]

Hi PeterH,


I have myself prepared a small vba code for you to fullfil your requirement however this is working fine for single formula cell.


Copy the below code to your module and just run the defineKey code only.

[pre]

Sub defineKey()
Application.OnKey "{F9}", "ShifttoAdjecentCell"
End Sub

Sub ShifttoAdjecentCell()
Dim rng As Range
Set rng = ActiveSheet.UsedRange
For Each cl In rng
If cl.HasFormula Then
a = cl.SpecialCells(xlCellTypeFormulas).Value
cl.Offset(0, 1).Select
ActiveCell.Value = a
End If
Next
End Sub

[/pre]
This can partly fullfil your requirement.


Also, add below line of code in workbook close event.


Application.OnKey "{F9}"


I am still working on this and will come back when get it worked in more proper way.


P.S.- As Luke advised above, this is not recommended, so please try above code on other practice sheet only.


Thanks & Regards,

Anupam Tiwari

 

[PeterH]

Hello Luke M,

If you mean manually add the results to a table, well yes I could do that but I do enjoy manipulating excel to do things automatically. It's half the fun for me.

Thanks for the suggestion.

Peter H

 

[PeterH]

Hello Anupam,

I am a bit of a VB novice.

I managed to open a new module, paste the code into it and saved it.

I couldn't find where to open a "workbook close event", do you have any suggestions where I should look? I am running excel 2003.

Thanks for the help.

PeterH.

 

[anupamtiwari05]

Hi PeterH,


To go to "Workbook close event", follow the below spteps:


1. Press Alt+F11

2. Press Ctrl+R if project window is not visible.

3. In Project window, you will see Microsoft Excel Object which will have worksheets name inside your workbook and last as ThisWorkbook.

4. Double click on ThisWorkbook object.

5. Now in VBA editor window, you will see two drop downs, first is general and second one is Declaration.

6. Click on General and select "Woekbook" and then click on Declaration and select BeforeClose

7. Now you will see Private Sub block, paste the VBA code for close event here and save it.

8. Its done now.


Thanks & Regards,

Anupam

 

[PeterH]

Hi Guys,

Thanks for your help.

I have worked out a way of achieving what I wanted using a macro assigned to a button.


If say A1 has my formula in it.

I cut and "pasted special" (values only) A1 - A30 TO A2 - A31 and then recalculated A1 with F9 button.

Every time the button is pushed, the 30 results shift down the page and a new value appears in A1.


It is a shame in a way that I came up with this non VB solution as it would have forced me to learn a little bit of VB which wouldn't hurt.

Maybe next time.

Thanks again for all of your trouble and effort,

Your fellow EXCEL wrestler,

Peter.