Solved: Is this possible

[blackie42]

Hi,

Have a process where I have to transfer individual items from a bulk number (held in one cell) to other bulk number(s) (held in different cells). At the moment I'm doing it by Cell A1: = 10,000.00-23.56-44.45 (total displayed is then 9931.99 - Cell A12: = 5000.00+23.56+44.45 (total displayed is 5068.01).

So basically transferring cash amounts between cells by use of +/-.

I was thinking I could maybe create a userform to automatically update both cells by just entering the number - but my question is how do I keep the record of what I moved in the cells - I suspect doing this will just update the total - but I need to keep an audit of what was moved i.e. in the same cell format as doing it the long way +23.56 on one side -23.56 on the other.

Any ideas please

thanks

Jon

[rory]

I would have thought it would be easier to have a range of cells showing your cash amounts and then have the other two cells simply add/subtract the total of that range?

[blackie42]

Nope the amounts I change are from discrepancy reports so I'd have to go to the trouble of loading them in to a range - not a time saver - decided don't need the audit - what I take from one I give to the other so overall the total cash will balance. Just need to set it up right.

thanks anyway

jon

[david000]

Option Explicit

Private Sub CommandButton1_Click()
Dim total As Variant, ans As Range
Set ans = Range("b2")
total = Range("b2").Value
With Sheet1
If Me.TextBox1 <> "" Then
total = total + Me.TextBox1.Value
ans = total
.Cells(.Rows.Count, "C").End(xlUp).Offset(1) = TextBox1.Value
End If
End With
End Sub

Private Sub CommandButton2_Click()
Dim stotal As Variant, ans As Range
Set ans = Range("b2")
stotal = Range("b2").Value
With Sheet1
If Me.TextBox2 <> "" Then
stotal = stotal - Me.TextBox2.Value
ans = stotal
.Cells(.Rows.Count, "D").End(xlUp).Offset(1) = TextBox2.Value
End If
End With
End Sub

Private Sub CommandButton3_Click()
Me.Hide
End Sub


Columns C & D custom format = "+" #,##0 & "-" #,##0

[Aussiebear]

Hey David000, feel free to add a post even if it says solved. I had a feeling from post #3, that the OP had marked it as solved because of frustration rather than having a correct answer.

I'm confident that Jon will respond accordingly soon.

[blackie42]

Hi Guys,

Sorry for not responding - I closed the Thread as I haven't had time to tinker with possible solutions and don't like keeping Threads open to long.

However the code & userform above are welcome - problem with it is it 'bugs' out on 'range b2'

thanks

[blackie42]

Actually - just tried it again and it works perfecto - Will adapt this to my situation/

Thanks to David000 for the steer

Jon

[Aussiebear]

There you go... a win win situation for both David & Jon. Well done guys.