Multiply Every 2 Cells
Brilliant, does exactly what I want. I now need it to multiply every 3 cells,
but I should be able to work that out myself.
Thanks,
Dave
"NickHK" wrote:
Dave,
I guess the name "RunTotal" has not been created. Select another sheet and
come back to that, so the WS_Activate event fires.
Then try again
NickHK
"David M C" ...
Hi Nick,
Thanks for your help.
On entering the second number, I get an error (Runtime Error '1004'). It
fails on:
Range("RunTotal").Formula = ""
Any ideas
"NickHK" wrote:
Dave,
OK, try this. It's a bit shaky and need more error handling, but should
get
you started. There probably better ways of doing it, but ...
Assumes numbers entered in pairs
Private Sub Worksheet_Activate()
On Error Resume Next
If Names("RunTotal") Is Nothing Then
Call MakeRunTotal
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1) And Target.Count = 1 Then
If Application.WorksheetFunction.CountA(Range("A:A")) Mod 2 = 0 Then
'Check "RunTotal" is valid: If not call MakeRunTotal
Range("RunTotal").Formula = ""
Target.Offset(0, 1).Formula = "=R[-1]C[-1]*RC[-1]"
Names("RunTotal").RefersTo = "=" & Target.Offset(2, 1).Address
Range("RunTotal").Formula = "=SUM(B1:B" & Target.Row & ")"
End If
End If
End Sub
Private Sub MakeRunTotal()
Names.Add Name:="RunTotal", RefersTo:="=$B$" & UsedRange.Rows.Count + 2
Range("RunTotal").Formula = "=SUM(B1:B" & UsedRange.Rows.Count + 1 & ")"
End Sub
NickHK
"David M C" wrote in message
...
Yes, accept it has to work when a line is skipped.
A B
1] 3
2] 2 =A1*A2
3] 3
4] 2 =A3*A4
5]
6] 3
7] 4 =A6*A7
Ideally, under the last total in column B, there will be a running sum
of
all the values in column B too.
"NickHK" wrote:
Dave,
You mean:
A B
1]2
2]2 =A1*A2
3]3
4]2 =A3*A4
Either enter the formulae manully, or in the Worksheet_change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Row Mod 2 = 0 Then
Target.Offset(0, 1).Formula = "=R[-1]C[-1]*RC[-1]"
End If
End If
End Sub
NickHK
"David M C" wrote in message
...
Is there a way I can get Excel to multiply every 2 cells in a given
row?
The
difficulty being that it needs to multiply as the data is entered,
not
by
running a macro after all data entry is finished.
For instance, I want to be able to enter in A and total in B:
A B
2
2
4
3
2
6
etc
Is it possible to set this up as a seperate form, where the sum of
column
B
goes into the cell the form is opened from?
Thanks,
Dave
|