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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

Joined
Sep 4, 2003
Messages
4,916
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
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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:
 

Attachments

Joined
Sep 4, 2003
Messages
4,916
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.
 

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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top