[RESOLVED] simple vba function

[InderpalHothi]

Hi guys,

i am fine with sub routines, first time i am trying a function.


Function Value1(ByRef p As Range, ByRef d As Range) As Variant


If p.Value = "" Then p.Value = 0

(**if i but "end if" here i get a block if error)

If d.Value = "" Then d.Value = 0

(**if i but "end if" here i get a block if error)


Value1 = p.Value + d.Value


End Function


Bascially based on whether or not a number is present (there is a formula in the cell to determine whether or not a number will be in a cell), i want to it to sum each of the values of the two ranges. If one of the two cells is empty, i want it to assign 0. I keep on getting a value error if one of the values is empty.

[westconn1]

If p.Value = "" Then p.Value = 0

this tries to change the value of the range, which you can not do within the function, try like

Function value1(p As Range, d As Range)
value1 = CDbl(p.Value) + CDbl(d.Value)
End Function

the conversion to double will automatically assign 0 to empty cells, you can use cint or clng etc, what ever conversion is most appropriate for possible values

[InderpalHothi]

thanks, will give this a try and mark resolved if it works...