Problem with UDF

[mala]

I am getting the data in the form of 2/5, 10/18, 5/8 etc.
I need to sum the figures to the left of "/" (slash)and right of "/"
(slash)
To acheive this I am creating two UDF for left and right each.
So far I have this :

Function slsum(kk) As Range
Dim mysum
mysum = 0
For Each cell In kk
'=+LEFT(A2,FIND("/",A2)-1) ----This formula works when entered in cell
pp = WorksheetFunction.Sum(Left(kk, WorksheetFunction.Find("/", kk) -
1))'*ERROR LINE*
mysum = mysum + pp
Next
slsum = mysum
End Function

The error line marked above gives me error of type mismatch.
If I give range as single cell, last line slsum=mysum gives error of
"Object variable or with block variable not set"

Request for solution please.
Regards,
Manakmala.

 

[Tom Ogilvy]

if you have excel 2000 or later

for each cell in kk
varr = split(cell.value,"/")
pp = clng(varr(0)) + clng(varr(1))
Next

 

[Michael Malinsky]

The problem is in how you made the declarations in your function line in
addition to referring to kk as opposed to cell in your pp line. The
Function line should read something like:

Function slsum (kk as Range)

Then in your line where you pick out the value to the left of the slash, you
refer to kk, the range, rather than cell, which is the individual value that
you want to look at. I took the liberty of modifying your code to return a
result appearing in the same format as your input data. I also changed some
variable names for clarity. This is under the assumption that the values
are text values, not decimal values formatted as fractions, which won't
work.

Here's the code:

Option Explicit
Private Function slsum(sumrange As Range)

Dim leftsum As Integer
Dim rightsum As Integer
Dim leftvalue As Integer
Dim rightvalue As Integer
Dim cell As Object

leftsum = 0
rightsum = 0

For Each cell In sumrange
If cell <> "" Then
leftvalue = (Left(cell, WorksheetFunction.Find("/", cell) - 1))
'*ERROR LINE*
leftsum = leftsum + Val(leftvalue)
rightvalue = (Right(cell, Len(cell) -
WorksheetFunction.Find("/", cell)))
rightsum = rightsum + Val(rightvalue)
End If
Next
slsum = leftsum & "/" & rightsum

End Function


--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh

 

[keepITcool]

in addition to Tom's remark:

your function is declared to return a Range object
but i think you just want to return a value...


either:
Function ReturnARange(strAddress as string) as range
SET returnarange = Range(strAddress")
End Function

or:
Function ReturnAValue(kk as range) as Double
kk = application.worksheetfunction.sum(kk)
End Function

Question to Tom:
Any books on comAddins that you can recommend?


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepITcool]

so bothered he even shortens his own name to Winne
<g>