Hello,
i am developing two fuctions for a worksheets that performs calcuations on three columns of data that are manually inserted. I have created the fuctions as well a a sub() that inserts the equations and uses Selection.AutoFill to fill out over my range. and this works as intended.
The first fuction similarly does what i desire it to, where if values are entered in a certain data column then the fuction is computed and a value is returned, if the cell is blank a null string is returned, so i have the equation in all cells and if data is entered it calculates and displays, otherwise its appears blank
the function that does this is:
Function iFunc1(input1, input2, input3 As Double) As Variant
'the variable names and equation have been simplified for this post
If input1 > 0 Then
iFunc1 = (input1 * 20 * input2) / (30 * (40 + input3))
Else
iFunc1 = vbNullString
End If
End Function
I believe this works because of the cell that the If statement runs on is truely equal to zero, no just a cell that appears as such,
Which leads to my problem, my second fuction is performing a change to the column of data that the first function computed, ive set it up in the same way and it does the calculation correctly when it does it, but if the previous column was left blank by the previous equation it returns #VALUE to the cell
I would like it to similarly appear blank or equal 0 both for visual purposes, but also so i can do calcualtions such a SUM() on the range without it also returning #VALUE
the second function is (note this starts in row two where row one has a zero, this lets us add previous value to current value):
Function total(currVal, prevVal As Double) As Double
If currVal.Value = vbNullString Then
total = vbNullString
ElseIf currVal.Value <> 0 Then
total = currVal + prevVal
End If
End Function
Thank
i am developing two fuctions for a worksheets that performs calcuations on three columns of data that are manually inserted. I have created the fuctions as well a a sub() that inserts the equations and uses Selection.AutoFill to fill out over my range. and this works as intended.
The first fuction similarly does what i desire it to, where if values are entered in a certain data column then the fuction is computed and a value is returned, if the cell is blank a null string is returned, so i have the equation in all cells and if data is entered it calculates and displays, otherwise its appears blank
the function that does this is:
Function iFunc1(input1, input2, input3 As Double) As Variant
'the variable names and equation have been simplified for this post
If input1 > 0 Then
iFunc1 = (input1 * 20 * input2) / (30 * (40 + input3))
Else
iFunc1 = vbNullString
End If
End Function
I believe this works because of the cell that the If statement runs on is truely equal to zero, no just a cell that appears as such,
Which leads to my problem, my second fuction is performing a change to the column of data that the first function computed, ive set it up in the same way and it does the calculation correctly when it does it, but if the previous column was left blank by the previous equation it returns #VALUE to the cell
I would like it to similarly appear blank or equal 0 both for visual purposes, but also so i can do calcualtions such a SUM() on the range without it also returning #VALUE
the second function is (note this starts in row two where row one has a zero, this lets us add previous value to current value):
Function total(currVal, prevVal As Double) As Double
If currVal.Value = vbNullString Then
total = vbNullString
ElseIf currVal.Value <> 0 Then
total = currVal + prevVal
End If
End Function
Thank
