Need formula / macro help

[klarowe]

I have a worksheet that I use for calculating my banking / bills. Basically in column C I have a running total and in column B I have the debit / credit amount.

Currently I have just "=C5+B6" throughout all of column C (with 5/6 changing depending on the actual cell).

Is there a way to "automate" this so that if I need to add a row in the middle, I don't have to go through and rewrite all the equations in the following cells??? However I need it to still allow me to change the values in Column C when needed (to update current available funds if it changes). I started with trying to write a selectionchange macro using offsets, but it didn't work out too well.

Here is a real quick example of how it is setup:

I am fairly familiar with macros but I don't even know how to bring up the "spreadsheet formulas" box... lol

Any help is appreciated.

 

[adam087]

Use in C3...
=SUM($B$2:B3)

Then copy down all the way so in C9 (for example) it will become
=SUM($B$2:B9)

You'll need to put your 'starting' value of 500 into B2 to make it work like yours does.

Regards
Adam

 

[klarowe]

Thats mostly there. the only issue I still have is that if I have to insert a row (add a bill for example) the code continues working after that point but leaves the C column blank in that row since the row was added after the formula was copied down.

This is where my problem is... which I wasn't sure if it could be done by a formula or if I had to use a code involving offsets.

Thanks for the help!

 

[klarowe]

It may be useless, but here is the code I have written trying to get it to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    For i = 1 To 3000
        Set Rng = Range("C" & i)
        With Rng
            balance = Rng.Offset(-1, 0).Value
            addition = Rng.Offset(0, -1).Value
            Rng.Value = Application.WorksheetFunction.Sum(balance, addition)
        End With
    Next i
End Sub

But I keep getting an "Run-Time error 1004: Application-defined or object-defined" error at "balance = Rng.Offset(-1, 0).Value"

 

[klarowe]

I think I've got it all figured out via VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    For i = 3 To 3000
        Set Rng = Range("C" & i)
        With Rng
            balance = Rng.Offset(-1, 0).Value
            addition = Rng.Offset(0, -1).Value
            Rng.Value = Application.WorksheetFunction.Sum(balance, addition)
        End With
    Next i
End Sub

I think I was screwed up because I was setting i starting on the first row which threw the offset off.

Everything seems to be working properly
Thanks

 

[klarowe]

Well I thought I had it working 100% but I did find a slight issue. There will be some instances where I will need to manually enter data into Column C (updating balance when needed). Is there a way to set it up so that a manual entry will over-ride the macro entry?

And another slight issue, is there a way to stop the loop when there is no value in Col. B so that I don't have the last balance repeating itself for all 3000 rows?

 

[adam087]

Perhaps a better method than overwriting a calculated value in C would be to insert a correcting value into a new row in B. This would leave a better audit trail as well.

My orginal formula shouldn't complain if you insert a row although you will need to fill the forumla into the blank space?

Regards
Adam

 

[klarowe]

Missed the edit, but I figured out the second part of the above post... still need help on the first part

 

[klarowe]

Perhaps a better method than overwriting a calculated value in C would be to insert a correcting value into a new row in B. This would leave a better audit trail as well.

My orginal formula shouldn't complain if you insert a row although you will need to fill the forumla into the blank space?

Regards
Adam

I did think of that and may end up being what I do... but I figured I would see if there was a better solution. And yes, your formula did work fine with just having to fill it into the space, but again, I was just trying to find something easier.

Thanks!!

 

[klarowe]

A little info into what I have this spreadsheet for:
I don't use it as a checkbook per say. Basically I use it on an account my wife and I have set up that all of our bills come out of. We also have extra money deposited into that account for trips or weekend outings that we go on. So i keep it updated with the amount coming and and going out of the account from bills and deposits so that I can keep a tab of what "extra" money is in the account (as to not affect any bills coming out).

I do not enter in the "extra" expenses that come out of the account which is why I have the need to constantly be updating the balance. That way I can log online say after a weekend outing, update the current balance and make sure that there will not be any issues with upcoming bills.

I know its a little odd and probably hard to understand, but hell... I'm an odd kind of person.