Query of cells addition

PANKAJUTEKAR

Board Regular
Joined
Jun 10, 2011
Messages
79
Dear All,

In Aftrenoon, Mr. Sektor Sir has given me the following code for cells addition containing string values.
but this code i have not understand.

Function SumKW(ParamArray arr() As Variant) As Double
Application.Volatile
Dim i As Integer, res As Double
Dim cell As Range
For i = LBound(arr) To UBound(arr)
For Each cell In arr(cell)
res = res + Left(cell, Len(cell) - 2)
Next
Next
SumKW = res
End Function

I wrote this code in vba workbook.
what he has suggested me, in workbook, in cell c115 (here i want total),
put formula as "=SumKW(c7,c112)"

But i am not getting how this "SumKW" function will work after i wrote in cell 115.

Please All, make me understand this query.
This is very imp for me to solve my problem.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sir,

In Col C-
range c7:c112 contains value.
i.e- c7= 5.5kw
c8 = 15.5kw
c9 = 25.5kw
and so on till c112.

whenver i try to add to all this gives an error, bcoz of cell contains a string value.

Therfore, i require some idea .. or anything the addition will done in c115 along with the o/p kw.

for eg.- in c115 o/p is = 1022.55kw
like this.
Can u pls solve this problem Sir?

and some of the cells have blank value also..like c11
 
Last edited:
Upvote 0
Pankaj,

Try the following UDF instead i.e. similar to SUM (except handling kw string)
Function SumKW(r As Range) As String
'Function will recalculate automatically
Application.Volatile
'Variables for calculations
Dim dCellKWvalue As Double, dTotal As Double
Dim cell As Range
'Sum formula in essence
For Each cell In r
dCellKWvalue = Left(cell, Len(cell) - 2)
dTotal = dTotal + dCellKWvalue
Next cell
'adding kw string to the calculated total
SumKW = dTotal & "kw"
End Function
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top