Solved: Microsoft excel 2003 substitue and find again 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

Thanks for your help

I have a nominated start date that will be different for different sheets.
I have tables with the same date as the nominated start date.
I'm trying to put a macro together that will grab the nominated start date and then go the Data1 table and find the same date.
Once i have this date I want to be able to go to any column and then sum the data up to the top of the table.

Can someone tell me if I am on the right track or can you see a better way of doing the above. I have attached a sheet with a few macros to try and do this, but just not getting there. Below is one that looked promising, but I don't fully understand the commands.

Sub SumData()
'Find the same date and then sum the data in any column I choose to the top of the table

Dim Mth As Variant
With WorkhsheetFunction
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) 0,2).Offset(, 1).Resize(, 1).Select
End With
End Sub

Thanks
Strybes
 

Attachments

Joined
Dec 19, 2008
Messages
744
try this...

Code:
Sub sumtomonth()
Dim TableName As String
Dim TR As Long, TY As Long
TableName = "Data1"
With Cells.Find(TableName)
TY = .Offset(3, 0).Row
TR = .Offset(2, 0).End(xlDown).Row
End With
Range("G7") = "=SUMPRODUCT((B" & TY & ":B" & TR & ")*(A" & TY & ":A" & TR & ">=G2))"
End Sub
You can change the table by changing the string that goes to the variable for TableName
 

Strybes

Thread Starter
Joined
Sep 7, 2007
Messages
67
Thanks turbodante, you've been a great help

I forgot to mention that the Start Date: may not always be on the same cell. As more data lines are added, it moves continually down, so i need to replace G2 in this example with part of the macro that enables me to replace G2 with a find and offset while picking up the date value.
Also where the formulae needs to go will also move down. I was able to get half way by doing the following, but couldn't get it to work picking up the date. I'm nearly there, but the formulae is putting the date in as 31/7/2009 instead of 31/07/2009.

Sub sumtomonth()
Dim TableName, Mth As String
Dim TR As Long, TY As Long
TableName = "Data1"
With Cells.Find(TableName)
TY = .Offset(3, 0).Row
TR = .Offset(2, 0).End(xlDown).Row
Mth = Cells.Find(What:="Start Date:").Offset(0, 1)
End With
Cells.Find(What:="Start Date:").Offset(5, 1) = "=SUMPRODUCT((b" & TY & ":b" & TR & ")*(A" & TY & ":A" & TR & ">=" & Mth & "))"

End Sub

Thanks Strybes
 

Strybes

Thread Starter
Joined
Sep 7, 2007
Messages
67
Thank you very much turbodante
I had just worked out that the only way to make it work was to not have the actual date show in the formulae, but keep the cell reference or address. I just didn't know how to go about it, but that was exactly what i needed.
Thank you so much.

On a personal note do you contract your services out??
 
Joined
Dec 19, 2008
Messages
744
Thank you very much turbodante
I had just worked out that the only way to make it work was to not have the actual date show in the formulae, but keep the cell reference or address. I just didn't know how to go about it, but that was exactly what i needed.
Thank you so much.
Great stuff (y)

On a personal note do you contract your services out??
Not as such - I just do this for fun like a lot of the other guys here. I think you'll find there are those with higher ninja excel skills than myself, doing stuff for free on these boards as a bit of procrastination from the day job.
 
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

Members online

Top