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?

 

[PANKAJUTEKAR]

I am sorry but please help on following..

Is this the right way?

and i type B115: =SumKW(B67:B112) ...this also i got "#name?" error.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Call SumKW
End Sub

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

 

[Sektor]

I see you don't understand what is UDF. UDF is User-Defined Function. You use it like regular Excel function. SumKW function has parameter. In our case it takes ranges, that's why I wrote example: =SumKW(B67:B112). You type it in a sheet.
Of course, you can call it from VBA, but then you must pass it correct ranges.

 

[PANKAJUTEKAR]

OK.
Sir, lets take an example..
suppose, c1 and c112 all sum require in c115.

In this case ... c115 i am able not put in this code...not able recognize.

Function SumKW(ParamArray arr() As Variant) As Double
Application.Volatile
Dim i As Integer, res As Double
Dim cell As Range...................how this red mark cell know that the total should go in c115? ...... I am trying to understand this expert logic, Sir!!

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]

OK.

- I wrote a the function u give it to me as it is.

- i called this function in,
"Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)"

- i typed the formula also in c115 that,
"=SumKW(c7:c112)
but the error is coming.

I understand that SumKW is function and inside that i passed parameter and this is totally depend on the function coding.

But Sir, the code u had given me, should i made any changes in that?

Everytime u help me and that is y, i learned somehow.

 

[Sektor]

It depends on WHERE you want to use function. Worksheet or UDF? If you call it from UDF and make Application.Caller, then you'll get error because Caller can be used if called from worksheet.