sheet before update

[tabaki]

I have a worksheet where in I calculate the costprice of new projects.
On each row I calculate the price for each part. Some times I need 4000 parts so I have a lot of rows.
Above I calculate the totals for each column (in VBA). Each column is a diffrent type of work
Now my problem: when I change the price of a row I want to change the totals immeadiatley. When i should use the complete procedure in VBA starting on each change_event, my system would work to slow. That is why I would store the old row in a temporary object. After my change I would look for the diffrence between the old object and the new object and adjust the totals in this way.
now my question: to do this I need a sort of before change event to store the old object; I know it exists in VB but it doesn't seem to exist in VBA. How do I do this

 

[macropod]

Hi tabaki,

Why do you need vba at all?

A simple formula like:
=SUM(OFFSET(B1,1,0,4000))
at the top of each column (B for the example above) should do the job.

If you're worried that you'll need more rows, you could increase the 4000 to, say, 5,000.

If you need something more dynamic, then, assuming at least one column (eg A) has every row filled, you could use a simple formula at the top of each column. For example, in Column B:
=SUM(OFFSET(B1,1,0,COUNTA($A:$A)))

Alternatively, if Column A has some blank rows along the way, but the last row is always populated and:
. the intervening rows always only have text, the following formula will do the job:
=SUM(OFFSET(B1,1,0,MATCH("*",A:A,-1)))
. the intervening rows always only have numerics, the following formula will do the job:
=SUM(OFFSET(B1,1,0,MATCH(1E+306,$A:$A,1)))
. the intervening rows have a mix of text and numerics, the following formula will do the job:
=SUM(OFFSET(B1,1,0,MAX(IF(ISERROR(MATCH("*",$A:$A,-1)),0,MATCH("*",$A:$A,-1)),IF(ISERROR(MATCH(1E+306,$A:$A,1)),0,MATCH(1E+306,$A:$A,1)))))

Cheers

[MS MVP - Word]

 

[tabaki]

The program i am developping is really a amelioration of an existing program. The existing file had hidden columns where some intermediair sums were made. This we thought was then the only possibility to calculate the totals. The problems came when unknowing people used this file and forgot to copy those hidden sums. In that way the total sum was not right
Your solution may be a solution for common sums, but I don't know if my sums can be made with your solution:
for each row I have the following fields:
f.i.

Item / number / shop1 / shop2 / shop3
Car 2 25.000€ 20€
Wheels 10 50€ 10€

Total: 50.500€ 100€ 40€

so what I need as extra is that the price of every item has to be multiplied with the number that I need for each item. Is this possible with your solution

 

[macropod]

Hi tabaki,

You can certainly do what you want with formulae, but I'm going away for a few days and don't have time to go through the process right now. I'll check again when I return and, if someone else hasn't already provided a solution, I'll have a got then.

Cheers

[MS MVP - Word]

 

[xlbo]

In terms of what has been asked for, you can implement this using the selection_change event (as opposed to the change event). If you use a public variable, you can then check the value against the new value in the change event...

eg:

Public OldValue As Integer, NewValue As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
NewValue = Target.Value

MsgBox "Old value was: " & OldValue & vbCrLf & _
        "New value is: " & NewValue
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldValue = Target.Value
End Sub

copy and paste into a sheet module and then start entering / changing values to see it working...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macropod]

Hi tabaki,

If it's the totals you need, you could use an array formula like:

=SUM($B$2:$B$3*C2:C3)
where the:
. number is in Column B
. shop price is in Column C
. items are on rows 2 & 3
to generate the total for Shop 1

In your example, copying the formula across columns D & E will generate the totals for Shops 2 & 3.

Note that this is an ARRAY formula. When you enter one of these, you press Ctrl-Shift-Enter - not just Enter - and Excel will enclose the formula in braces, like so:
{=SUM($B$2:$B$3*C2:C3)}

Cheers
PS: you mention having up to 4000 items. Just be aware that array formulae can be slow to recalculate with so many rows.


[MS MVP - Word]