Assigning Selected Cells to Code
 

I wish to onscreen (example only) select (highlight) say B5:B7, then (with
control key down) then select C10:C11; then run a macro that will produce a
MsgBox with:

"The Difference is 1,234.50"

B5 = 1,111.00
B6 = 2,222.00
B7 = 3,333.00 where B5:B7 total 6,666,00

and
C10 = 2,500.00
C11 = 2,931.50 where C10:C11 totals 5,431.50

Any help appreciated,,

Assigning Selected Cells to Code
 

I think you have to split it up. Test this hastily mixed code:

Add a form to your testsheet and a CommandButton1 in the form. In the
CommandButton1_Click() kode, add the following:

----------
Dim Info1 As String
Dim HoldRange
Dim i As Integer

For i = 1 To 2
Info1 = "Select the cells to include." & vbCrLf
Set userRange = Application.InputBox(prompt:=Info1, Title:="Calculate",
Type:=8)

HoldRange = userRange.Address
Set userRange = Range(HoldRange)

Range(HoldRange).Select

Cells(2 + i, 2).Formula = "=SUM(" & HoldRange & ")"

Next i

ssum = Cells(3, 2).Value - Cells(4, 2).Value

MsgBox (ssum)

Unload Me
Canceled:

---------
Take notice that the values are placed in Col B and that it seems you'll
have to use the mouse to select the cells.

Regards Birger

"JMay" skrev i en meddelelse
news:TSaSa.1928$If5.1696@lakeread06...
I wish to onscreen (example only) select (highlight) say B5:B7, then (with
control key down) then select C10:C11; then run a macro that will produce
a
MsgBox with:

"The Difference is 1,234.50"

B5 = 1,111.00
B6 = 2,222.00
B7 = 3,333.00 where B5:B7 total 6,666,00

and
C10 = 2,500.00
C11 = 2,931.50 where C10:C11 totals 5,431.50

Any help appreciated,,

Assigning Selected Cells to Code
 

Bob, one last Q - How could I format the code line

MsgBox "Difference is " & Application.Sum(Selection.Areas(1)) -
Application.Sum(Selection.Areas(2))

to reflect the number as say 1,234,567.00 where Selection.area(1)
is 5,999,999.00
and Selecton.Area(2) is 4,765,432.00?
I can't quite master the Format(Whatever,"0,0.00") thing.
Tks,

"Bob Phillips" wrote in message
...
Try this macro

Sub Difference()
If Selection.Areas.Count 2 Then
MsgBox "Too many areas selected"
Else
MsgBox "Difference is " & Application.Sum(Selection.Areas(1)) -
Application.Sum(Selection.Areas(2))
End If
End Sub


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"JMay" wrote in message
news:TSaSa.1928$If5.1696@lakeread06...
I wish to onscreen (example only) select (highlight) say B5:B7, then
(with
control key down) then select C10:C11; then run a macro that will
produce
a
MsgBox with:

"The Difference is 1,234.50"

B5 = 1,111.00
B6 = 2,222.00
B7 = 3,333.00 where B5:B7 total 6,666,00

and
C10 = 2,500.00
C11 = 2,931.50 where C10:C11 totals 5,431.50

Any help appreciated,,

Assigning Selected Cells to Code
 

Much appreciated Tom..


"Tom Ogilvy" wrote in message
...
MsgBox "Difference is " & format(Application.Sum(Selection.Areas(1)) - _
Application.Sum(Selection.Areas(2)),"#,###.00")

Regards,
Tom Ogilvy


JMay wrote in message
news:psdSa.1940$If5.1546@lakeread06...
Bob, one last Q - How could I format the code line

MsgBox "Difference is " & Application.Sum(Selection.Areas(1)) -
Application.Sum(Selection.Areas(2))

to reflect the number as say 1,234,567.00 where
Selection.area(1)
is 5,999,999.00
and Selecton.Area(2) is 4,765,432.00?
I can't quite master the Format(Whatever,"0,0.00") thing.
Tks,

"Bob Phillips" wrote in message
...
Try this macro

Sub Difference()
If Selection.Areas.Count 2 Then
MsgBox "Too many areas selected"
Else
MsgBox "Difference is " &
Application.Sum(Selection.Areas(1)) -
Application.Sum(Selection.Areas(2))
End If
End Sub


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"JMay" wrote in message
news:TSaSa.1928$If5.1696@lakeread06...
I wish to onscreen (example only) select (highlight) say B5:B7, then
(with
control key down) then select C10:C11; then run a macro that will
produce
a
MsgBox with:

"The Difference is 1,234.50"

B5 = 1,111.00
B6 = 2,222.00
B7 = 3,333.00 where B5:B7 total 6,666,00

and
C10 = 2,500.00
C11 = 2,931.50 where C10:C11 totals 5,431.50

Any help appreciated,,

Assigning Selected Cells to Code
 

Glad you like it. Sorry I wasn't around to respond to the follow-up, but I
see old Tom nipped in there.

Regards

Bob

"JMay" wrote in message news:tvbSa.1930$If5.459@lakeread06...
Bob, this is great!! Your macro makes up for the shortcomings of the
AutoCalculate feature that is missing a much needed "difference"
tabulation...
On screen all too often I've had to design it with certain cells "IN THE
NEGATIVE" just so I could use the Auto-Calculate Sum to get a quick
"Difference".
Thanks a million..
JMay

"Bob Phillips" wrote in message
...
Try this macro

Sub Difference()
If Selection.Areas.Count 2 Then
MsgBox "Too many areas selected"
Else
MsgBox "Difference is " & Application.Sum(Selection.Areas(1)) -
Application.Sum(Selection.Areas(2))
End If
End Sub


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"JMay" wrote in message
news:TSaSa.1928$If5.1696@lakeread06...
I wish to onscreen (example only) select (highlight) say B5:B7, then
(with
control key down) then select C10:C11; then run a macro that will
produce
a
MsgBox with:

"The Difference is 1,234.50"

B5 = 1,111.00
B6 = 2,222.00
B7 = 3,333.00 where B5:B7 total 6,666,00

and
C10 = 2,500.00
C11 = 2,931.50 where C10:C11 totals 5,431.50

Any help appreciated,,