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 substitue and find again help needed

Discussion in 'Business Applications' started by Strybes, Apr 1, 2010.

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

    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
     

    Attached Files:

  2. turbodante

    turbodante

    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
     
  3. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Note: if there is dirty data in the columns, you will get an error.
     
  4. Strybes

    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
     
  5. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    You're close.

    The needed this

    Mth = Cells.Find(What:="Start Date:").Offset(0, 1).Address
     
  6. 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??
     
  7. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Great stuff (y)

    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.
     
  8. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Well, I really appreciate it.
    Thanks again
     
  9. 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/913998

  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