I want a special Sum function but I need help with that

[jpballegeer]

I have the following issue, I will make a simplefied explenation here.

I have the Following Data

A B C
1 1
2 V
3 V
4 1
5 1
6
7 V
8 1
9 V
10 1 5

I have to count the total of a predefined, but dynamic range.

The Value of C10 should be the sum of B9:B5 (so 5 rows up). But if for
example the value of A9 is a V, the sum is going to be B9:B4, because
if there is a V in a row, there will be no value in the neighbouring
cell. So you see this is a dynamic calculation and needs a variabel

The Value in column A is ALWAYS V or nothing., The value in Column B is
is always 1 or nothing. The Value in Column C is the Sum.

(This in fact is a part of a calender where I need to calculate the
number of nights people worked in the field. It has to calculate the
number of nights in the field for the last 30 days but If it was a
National Holiday or I toke a day off, the number of absences have to be
added to the 30 days. This was no problem when I had a holiday for 5
days in those 30 days (it should count up to 35 cells) but when someone
has also a holiday on day 32 and 33 I needed to count 37 days etc.....
So in total it will be used in a 335 rows)

I want to do this via a macro in VBA because I want to use and
transform this function for use in other documents as well.

Can someone point me in the good direction? I haven't got much VBA
experience but I need some help.

greets

 

[jpballegeer]

I made some metacode how it should look like but I need more VBA help

' CounterDay represents the columnnr where the calculation has to
happen. Starts on row 31 untill row 365
' CounterV is the counter to count the number of V's he finds (it
stops counting when CounterN has reached 30
' CounterN Is Increased by 1 when the cell A(DAY-1-CounterV) is equal
to the Value "V"

CounterDay = 31

IF A(DAY-1-CounterV) = V
Then CounterV = CounterV+1
Else CounterN = CounterN+1
End If CounterN = 30

Sum=B(CounterDay-1):B(CounterDay-CounterN)
Put Sum in Cell C CounterDay
CounterDay + 1

End If CounterDay=365

 

[Bernie Deitrick]

JPB,

Enter the code below in to a codemodule, and use it like:

=SpSum(A1:A150,B1:B150,"V",30)

HTH,
Bernie
MS Excel MVP

Function SpSum(R1 As Range, _
R2 As Range, _
Ignore As String, _
Pop As Integer) As Double

Dim myRow As Long
Dim myCount As Integer

myCount = 0
SpSum = 0

For myRow = R2(R2.Cells.Count).Row To 1 Step -1
If R1(myRow).Value <> Ignore Then
SpSum = SpSum + R2(myRow).Value
myCount = myCount + 1
If myCount = Pop Then Exit Function
End If
Next myRow

End Function