Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing a range of Values (Access 2000)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Good Morning,

    B1=1 D1=6/25 E4=#Value! (but should =2)
    B2=0
    B3=1
    B4=0 D4=6/26

    Below is code to determine the total of B1..B3 and input the value in E1.
    I am not including B4 in determing the value because D4 has a different date than the rest.

    E1 has the following formula
    =IF(B1<>"",SumForDate(B1),"")

    Code:
    Function SumForDate(rng As Range) As Long
    Application.Volatile
    Dim rngAddr As String
    rngAddr = rng.Address
    SumForDate = (Range(rngAddr, Range(rngAddr).End(xlDown)).Sum)
    End Function

    However, E4 continues to return "#Value!" rather than the total which should be "2".

    Should I be referencing "D" because that's the cell the date resides in? If so, how should I change the code to Sum the values in the range between the date differences which is "B1..B3"?

    Can someone help me resolve this issue?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    Roberta,

    Is this similar to your previous scenario, in that the Date cell is blank (D, in this case) until a change of date occurs ?

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    Andrew,

    Yes this is the same scenerio. Still designing this spreadsheet so the user has as little input as possible. I want to know the sum of the cells in "B" for each date...once the date changes then the summing starts over for that period.

    Thanks Andrew for your help
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    Will this do what you want?

    <pre>Function SumForDate(rng As Range) As Long
    Application.Volatile
    Dim I As Integer
    SumForDate = rng.Value
    I = 1
    Do While (rng.Offset(I, 2) = "") And (rng.Offset(I, 0) <> "")
    SumForDate = SumForDate + rng.Offset(I, 0)
    I = I + 1
    Loop
    End Function</pre>

    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    Coleman,

    I tested your suggestion and put the following formula in E:

    =IF(D1<>"",SumForDate(b1),"")

    and still it returns a #VALUE!...do you have another suggestion? I would appreciate it.

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    I'm not sure what you did, but I am attaching the workbook I used to test this. It works fine for me.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    I just had another thought. After putting my code in the workbook, did you do anything to cause the formulas to recalculate?
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    Coleman,

    OK...i found out what i did wrong and corrected it and it works..EXCEPT...in the case of the following

    B1=1 D1=6/25 E1=2 (but should =3)
    B2=1
    B3=""
    B4=1
    B5=1 D5=6/26

    I think the reason is the OFFSET...once the offset determines a null value it stops summing...I put a 0 in B3 and still it quit summing at B2.

    Again I thank you for your help and welcome another suggestion.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    That is because the code is written to stop if the cells in column B and D are both blank. I did that so you could use the function in E5. The following will work for E1, but will fail if you put it in E5:

    <pre>Function SumForDate(rng As Range) As Long
    Application.Volatile
    Dim I As Integer
    SumForDate = rng.Value
    I = 1
    Do While rng.Offset(I, 2) = ""
    SumForDate = SumForDate + rng.Offset(I, 0)
    I = I + 1
    Loop
    End Function
    </pre>


    If you need to have empty cells in Column B and use the function for the last date group, then you need to find another way to tell the function when to stop.
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a range of Values (Access 2000)

    Legare,

    Thanks for your help, I found out the problem was in the Offset. The scenerio I posted was just that a scenerio...it wasn't the actual layout of my spreadsheet. Once I made the changes to the Offset..it worked.

    i.e. the column count between the Value and the Date in the actual spreadsheet is 10, so I changed the offset to 10 and everything is fine.

    My apologies is this threw u off course. I do appreciate your help.

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •