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

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

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

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

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

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

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