ScoobyNet.com - Subaru Enthusiast Forum - Excel help

ScoobyNet.com - Subaru Enthusiast Forum (https://www.scoobynet.com/)

- Computer & Technology Related (https://www.scoobynet.com/computer-and-technology-related-34/)

- - Excel help - possible Macro required? (https://www.scoobynet.com/computer-and-technology-related-34/603218-excel-help-possible-macro-required.html)

Excel help - possible Macro required?

Hi,

I currently have quite a large & complicated spreadsheet (a 3 year financial forecast for a corporate finance company).

At the moment I have to manually input the required monthly funding figure each month to make the figures balance. This has to be done manually as when the month 1 figure is entered it has a knock on effect on months 2 - 36 due to interest costs, funding requirements etc.

I have a formula that calculates the requirement each month (in the cell below where the figure is required) but I cannot use a formula to automatically calculate the figure as it is the final piece in the accounts jigsaw and creates a circular references! :brickwall

What I need excel to do is automatically input the correct figure for month 1 from the calculation in the cell below then once this is done and the figures for months 2-36 have automatically flowed through and changed take the updated month 2 figure, which will then change months 3-36 etc. etc.

Basically I need Excel to copy 36 results from 1 cell to another individually and inbetween each cell being copied the remaining cells to be recalculated.

Any suggestions chaps as it's driving me mad having to manually enter 36 figures each time I update the forecast :brickwall :brickwall

Hope you can understand my explanation LOL

Gareth

I've read this a couple of times but I can't quite work out what it is you need... but I am very tired :D

Do you have an example you can upload somewhere (fudge the figures if its sensitive)

D

I've uploaded a VERY simple example of what I need to do here

http://myweb.tiscali.co.uk/boogat/example.xls

Hopefully you'll be able to see what I mean :wonder: but bare in mind that is a very simple sheet with a lot less tabs/tables as are in the full sheet!

Why dont you record a macro, copy and paste the results manually for each month, then next time you need to recalculate a different figure just run the macro again.

Try This?

Code:

Sub UpdateFigures()

Dim icol As Integer

'1 to 12 (+ 5 for Column E)

For icol = 5 To 16

'First determine if funding req (Check Closing Bank)

'If not - Do nothing

If Cells(40, icol) >= 0 Then

'Otherwise

Else

    'Enter the Value in the cell (Absolute Value - Without the minus sign)

    Cells(6, icol).Value = Abs(Cells(11, icol).Value)

End If

'Move to next column

Next icol

End Sub