Editing a new total

Posted by M Walker on August 16, 2001 7:00 AM

Hi,

I have a userform that inserts a number into a spreadsheet which then alters total. The problem i'm facing is that these numbers placed onto the spreadsheet accumulate and give a false impression of the total. What i would like to do is replace the old total with a new total and clear the numbers placed in two columns. eg. copy a4 and paste it in a1 and clear a2 and a3.

Thanks in advance

Matt

Posted by neo on August 16, 2001 7:27 AM

can you be a bit more specific as to how your total's being 'altered'? and do i understand right that you have a column of numbers that come to a total at the bottom of it, and you want to take this total (when you're through entering numbers to get this total) and paste it up to the beginning of the column and delete the numbers entered to arrive at this total? did that make sense? I don't evn think it did to me...:-)

neo

Posted by M Walker on August 16, 2001 7:49 AM

Hi,

yer, looking at it again it was a bit vague. My workbook is essentially a stock list. The totals i mentioned are the total number of different types of stock eg. Single sockets = 100

There is no grand total if yuo like just the number of a particular items of stock. There is a product description in column A, an old (existing) total in B. Column C and D are used to add stock and remove stock whilst E is the new total (This is achieved using this formula =SUM((B#+D#)-C#). What i want to do is replace the old total with the new total and clear columns C and D when i exit the user form.

Hope that makes more sense.

Cheers,
Matt

Posted by neo on August 16, 2001 8:22 AM

matt,

i used cells a2:e5 to recreate what you're looking for. a2 has the product, b2 has the current (old) total, c2 i used for removing stock, d2, for adding stock, and e2 holds the new total... to get that new total to replace the old total (and clear out the add / remove columns) i used this code in the 'exit' button of the form:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Range("e2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("b2").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Application.ScreenUpdating = True
Unload Me
End Sub

that it?

neo