1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Microsoft Excel 2003 Sum Section using date to determine range help needed

Discussion in 'Business Applications' started by Strybes, Mar 30, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi All (please refer to entry #4 with updated excel)

    Thanks for helping.

    Thanks to Turbodante, I am able to sum a range of colunm data in a macro (refer below), but now I need to be able to choose how much of the column to sum using a date that it stops at.
    So rather than doing the whole table, I need it to just sum, as per the example attached, from July09 to the top of the table. Also each month additional data will be added, so its needs to be able to keep summing the data from the date to the top of the table as new data is entered each month.
    Also different sheets will need to pick up the date where it needs to end. This will be in the worksheet as a manual entry (refer cell E18)

    Sub Macro1()
    Dim bRow As Long
    bRow = Range("b4").End(xlDown).Row
    Cells.Find(What:="Cell Name").Offset(1, 0) = "=sum(b4:b" & bRow & ")"
    End Sub

    Thanks for your help

    Strybes
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    This should be very easy but the macro needs to know which starting date to use. You have a couple of options that could work for you. You can manually select the starting cell and then run the macro or you could embed a combobox on the sheet to have the user select the starting date from there. Do either of these sound like an option for you?

    Rollin
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I've also looked at your sample workbook and I see that you are putting a date in cell E17 ??? Is this supposed to represent the starting date? If so, please explain why this date (7/1/2009) does not exactly match the date showing in cell A10 (7/30/2009)

    It would be much easier if these dates matched exactly so that an exact match can be made using the built in FIND function in Excel without having to add special logic in VBA. This could easily be accomplished by using some simple data validation.

    Regards,
    Rollin
     
  4. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi Rollin_Again

    Ok, this is a bit more complicated then I thought.
    What makes it harder is that there can be up to 3 dates the same in the same worksheet. We usually work with at least two tables of data that contain different figures but related to the same months. So I need to be able to do the following:
    1. If we manually put the date in a cell, then grab that date
    2. Then we need to go to the table and grab data that could be under any title e.g. Income or Expenses that ranges from the top to the date that is in the table that we are looking for in point 1.

    I hope this makes sense.

    I have been trying a few things, but with no luck. I'm able to go to the cell with the date, but I'm not sure if I'm picking up the value and then have no idea of how to use that value to look again in the above table for the same date. my thought was that if I could select the same date in the above table, then I could offest to the column i want and sum xlup from there..as i said this is a bit above the L plates i have

    A couple of things I have tried that haven't worked:
    ________________________________________________________________
    Dim Mth As Date
    Mth = Cells.Find(What:="Start Date:").Offset(0, 1).Value
    'X = Application.Match("Data1", Range("A1:A500"), 0)
    'Y = Application.Match("Mth", Cells(X, 1).Resize(1), 0)
    'Cells((X + Y)).Select
    __________________________________________________________________
    Dim Mth As Date
    Mth = Cells.Find(What:="Start Date:").Offset(0, 1).Select
    ActiveCell.Select
    Cells.Find(What:="Mth", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=True).Activate
    _________________________________________________________________
    In playing around with this, I have discovered that even doing Edit/Find that unless the Date that i enter is exactly the same format as the dates I'm searching for it won't find it.

    Thanks for your help:confused:
     

    Attached Files:

  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The format of the date shouldn't be a problem for the find function as long as the actual value is the same. I'll play around with this when I get to work in a few hours.

    Rollin.
     
  6. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi Rollini_Again
    Yeah I would have thought the same, but when i changed the format and did find, it wouldn't find the date i entered. It was only when I used paintbrush to copy the same format that it found it again...bizzare.
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    What I meant was that the cell format wouldn't matter when the values are compared within the macro. The find function by itself doesn't do that, the logic has to be included within the macro. Sorry about the confusion.

    Rollin
     
  8. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hey Rollin_Again
    I'm confused regardless. :)
    Thanks for the explanation. I know what you are saying, but just don't know how to implement.
     
  9. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Thought it best to close this issue and start a new one.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/913703

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice