How to add random cells and get a sum value in another cell than status bar.
 

I want to make a function table in excel for example:
a: 10
b: 20
c: 30
d: 40

When I select "a" and "b" it should add it and give the value "30" in another cell.
Just like we select 3 or 4 numbers and it gives the value in status bar. instead of showing value in status bar I want it in cells if I select A /B / C.

How to add random cells and get a sum value in another cell than status bar.
 

Umi wrote:

I want to make a function table in excel for example:
a: 10
b: 20
c: 30
d: 40

When I select "a" and "b" it should add it and give the value "30" in
another cell.
Just like we select 3 or 4 numbers and it gives the value in status bar.
instead of showing value in status bar I want it in cells if I select A
/B / C.

Put this in the workbook's ThisWorkbook object:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim cel As Range, working As Variant
For Each cel In Target.Cells
working = working + cel.Value
Next
Sh.Range("A1").Value = working
End Sub

Replace "A1" with your target cell.

This does give problems if you mix numeric and non-numeric cells. There are
a few ways to deal with this:
1. Never select non-numeric cells.
Problem: Pain in the ass.
2. Trap the error - On Error Continue Next
Problem: Inconsistent results. (A+1 < 1+A)
3. Force numeric values - Val(cel.Value)
Problem: Non-numeric values are always treated as zero.
4. Treat non-numerics specially - If VarType(cel.Value) = vbString Then
Problem: Have to decide how to handle things like A+1.

--
- You don't sleep, do you?
- Does lying still count?

How to add random cells and get a sum value in another cell than status bar.
 

Hi,

Am Sat, 8 Oct 2016 09:58:43 +0100 schrieb Umi:

I want to make a function table in excel for example:
a: 10
b: 20
c: 30
d: 40

When I select "a" and "b" it should add it and give the value "30" in
another cell.

Alternative:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A1") = Application.Sum(Selection)

End Sub

ignores text values


Regards
Claus B.
--
Windows10
Office 2016

How to add random cells and get a sum value in another cell than status bar.
 

Claus Busch wrote:

Hi,

Am Sat, 8 Oct 2016 09:58:43 +0100 schrieb Umi:

I want to make a function table in excel for example:
a: 10
b: 20
c: 30
d: 40

When I select "a" and "b" it should add it and give the value "30" in
another cell.

Alternative:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A1") = Application.Sum(Selection)

End Sub

ignores text values

Derp. I almost never think to use worksheet formulae.

--
No main() No Gain!

How to add random cells and get a sum value in another cell than status bar.
 

Claus Busch wrote:

Hi,

Am Sat, 8 Oct 2016 09:58:43 +0100 schrieb Umi:

I want to make a function table in excel for example:
a: 10
b: 20
c: 30
d: 40

When I select "a" and "b" it should add it and give the value "30"
in another cell.

Alternative:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A1") = Application.Sum(Selection)

End Sub

ignores text values

Derp. I almost never think to use worksheet formulae.

This approach offers a great deal of flexibility over std VB functions.
You can also get most of this in your VB6 projects via the OWC
(spreadsheet control) hidden or visible on a form.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion