Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want a special Sum function but I need help with that
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want a special Sum function but I need help with that
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want a special Sum function but I need help with that
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 wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special Function | Excel Worksheet Functions | |||
Special Date Function | Excel Worksheet Functions | |||
Paste special function | Excel Discussion (Misc queries) | |||
Special function | Excel Worksheet Functions | |||
special function help needed | Excel Worksheet Functions |