Cells addition problem - very hectic

[PANKAJUTEKAR]

Dear All,

I have tryed this code, and also i called this function in workbook event (after save event).

But still i am got any success on this.

Is one of you, sole the problem?

I totally understand the code. but still not understand how to call it.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call SumKW
ThisWorkbook.Activate
'Dim i As Integer, j As Integer, k As Integer
'Do Until i = 112
' i = 0
' j = 3
'Cells(i, j) = Cells(7, 3)
'k = Left(cell, Len(cell) - 2)
'i = i + 1
'Loop
End Sub

Public 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

 

[Sektor]

I already told you that SumKW is FUNCTION! It takes Range object as parameter (variable r) as returns String. In your code I see "Call SumKW". It's incorrect 'cause you call it like a Sub. Moreover, you don't pass it any Range object.

 

[SaladProblems]

this is your function:

Public 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

The stuff on top is a different function that has no effect on it.

The way to use your function is simple: pick a cell and enter =sumkw(a1:a10) and it will perform this function on those cells. Put in whatever range you want.

This function will not work unless all values in your range have at least 3 digits.

 

[PANKAJUTEKAR]

Dear Sektor Sir and SaladProblem,

Thanks for your reply.

as per your suggestion i put in c115. following are the steps of action.

=SumKW(c7:c112) ....i entered

Error is - #Name?

Now what would i do?
even i put proper namespell of "SumKW"?
****kindly suggest****

 

[Sektor]

I already posted you modified function.

 

[PANKAJUTEKAR]

Sir,
last query..pls need your answer Sir.

You give me this function. but can u please tell me where should I write this function.

I wrote this in sheet1 - i go to coding part - project explorer - thisworkbook.
Is this the right way?

I know this is very basic question. But i think this should understand also.
Last Time...

 

[Sektor]

1. Alt+F11
2. Go: Insert -> Module
3. Paste that modified function.
4. Go to worksheet.
5. Type in a cell (example): =SumKW(A1:A10) or =SumKW(A1:A10,B14). In other words, any range would fit.

 

[PANKAJUTEKAR]

Yes....its giving me error..#value?
its change now...i checked the cells format..that is fine.

Is there any other problem?

 

[PANKAJUTEKAR]

ok...what haapened there are some values those containing
18.5 kw
now wht we had metioned
res = res + Left(cell, Len(cell) - 3)
so this case it will not work i think?

 

[Sektor]

Now all is gonna work OK! The problem was in extra space between number and "kw". Now it doesn't matter.

[COLOR="Blue"]Function[/COLOR] SumKW([COLOR="Blue"]ParamArray[/COLOR] arr() [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]

    Application.Volatile
    
    [COLOR="Blue"]Dim[/COLOR] re [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], res [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Double[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] cell [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]Set[/COLOR] re = CreateObject("VBScript.RegExp")
    [COLOR="Blue"]With[/COLOR] re
        .IgnoreCase = [COLOR="Blue"]True[/COLOR]
        .Pattern = "\s*kw\s*"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    [COLOR="Blue"]For[/COLOR] i = [COLOR="Blue"]LBound[/COLOR](arr) [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] cell [COLOR="Blue"]In[/COLOR] arr(i)
            res = res + Trim$(re.Replace(cell, vbNullString))
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]Next[/COLOR]
    
    SumKW = res & "kw"

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]