Calculation of cells with string value

[PANKAJUTEKAR]

Good Morning All,

I am beginer for Vba, i need all yours kind suggestion for following -

I have query about calculation of cells.
Problem is - cells containg
3.7kw
5.5kw
and so on.

Wht i was trying since morning...addition of this cells i required along with "kw" in the output. but when i am trying calculate this string value not accepted.

Is there any solution like str etc.... to accept and addition of this cells along with string?

 

[Sektor]

A1 = 3.7kw
A2 = 5.5kw

The sum is: =LEFT(A1,LEN(A1)-2)+LEFT(A2,LEN(A2)-2)

 

[mikerickson]

The approach I would use is to format the cells with a custom number format 0.0"kw" so that the number 2.3 would be displayed as 2.3kw, then conversion would be unneeded.

However if you are stuck with working with a string you could use
=left(a1,len(a1)-2)+0 to convert from the string "34.3kw" to the number 34.3

 

[PANKAJUTEKAR]

Thank You Sir for your kind reply.

But sir, how do i do this in vba coding?..........for multiple cells

 

[Sektor]

UDF. Summing.

Function SumKW(rng As Range) As Double

    Dim arr As Variant, i As Integer, res As Double
    
    arr = rng
    For i = LBound(arr, 1) To UBound(arr, 1)
        res = res + Left(arr(i, 1), Len(arr(i, 1)) - 2)
    Next
    
    SumKW = res

End Function

 

[PANKAJUTEKAR]

Thank You Very much sir.

 

[PANKAJUTEKAR]

Dear Sir,

One more problem is there -
a listbox contain some values.

"Private Sub Worksheet_Activate()" i added that items on worksheet active, also i was tryed on worksheet open also.
but the value of listbox not showing when i am opened the sheet newly.
i have to go to in coding part and do "alt - f11" then only they displayed.

So sir is there other for this?

 

[Sektor]

Improved version. Can take contigous and non-contigous cells.

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(i)
            res = res + Left(cell, Len(cell) - 2)
        Next
    Next
    
    SumKW = res

End Function

 

[PANKAJUTEKAR]

But Sir, how do i select the prpoer cell where i need the total sum.
suppose cells are b67:b112 this total sum display in b115.
This b115 i cant able to recognize.

can u please suggest for the above?

 

[Sektor]

Type in B115: =SumKW(B67:B112)

If you want to add non-contigous ranges, type in:
=SumKW(B67:B112,B55:B60,B40)