Function SubNSum(a As Double, c As Double, rng As Range) As Double
Dim x As Double
Dim y As Double
Dim z As Double
'Dim b As Double
'b = 0
'q-d>=o and <=f2
For Each Cell In rng
'x = a - c
x = Cell.Value - a
If ((x > 0) Or (x = 0)) Then
If ((x < c) Or (x = c)) Then
'b = b + 1
y = Cell.Row
z = Cell.Column
Cells(y, z - 1).Value = Cells(y, z - 1).Value + x
End If
End If
Next Cell
'SubNSum = b
End Function
Asked
Active
Viewed 21 times
0
-
4What's your question, or the error? – BigBen Jun 18 '20 at 18:06
-
This will not worked as UDF called from the worksheet. If it is not called from the worksheet but other vba code then make it a sub. – Scott Craner Jun 18 '20 at 18:10
-
its not adding anything to left cell – Rob Jr. Jun 18 '20 at 18:10
-
infact its giving #value| error – Rob Jr. Jun 18 '20 at 18:11
-
didnt get you scott craner – Rob Jr. Jun 18 '20 at 18:12
-
3A function called from the worksheet cannot change the value of any other cell than the one in which the formula is placed. – Scott Craner Jun 18 '20 at 18:12
-
No, I need a function – Rob Jr. Jun 18 '20 at 18:14
-
Not possible as a UDF called from the worksheet, except in some edge cases. – BigBen Jun 18 '20 at 18:14
-
oh, now i get it – Rob Jr. Jun 18 '20 at 18:14
-
will same thing work if turn this in to sub? – Rob Jr. Jun 18 '20 at 18:15
-
2Yes, because a `Sub` can modify the sheet. – BigBen Jun 18 '20 at 18:15
-
can I call both ranges here? will it affect that cell then? – Rob Jr. Jun 18 '20 at 18:16
-
You only need to pass one range; you can refer to the left cell with `Offset`. – BigBen Jun 18 '20 at 18:16
-
I have used offset with vloockup but how will I use it here – Rob Jr. Jun 18 '20 at 18:17
-
you cannot call a sub from the worksheet, it will need a trigger. – Scott Craner Jun 18 '20 at 18:19