Assigning Selected Cells to Code

[JMay]

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,,

 

[brym]

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:

 

[JMay]

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,

 

[JMay]

Much appreciated Tom..

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

Regards,
Tom Ogilvy

 

[Bob Phillips]

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