+ Reply to Thread
Results 1 to 9 of 9

Find a value in a database for the most recent date, return all values relating to it.

  1. #1
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Find a value in a database for the most recent date, return all values relating to it.

    Good evening Happy Campers,

    I return seeking wisdom from you Excel guru's (i thought i could do it without asking and as such am close to crying lol)

    So far i have a worksheet, on the tab 'raised' in reality there is hundreds of records, for now i cut it down to 5.
    On tab named 'Raising' i only want to manually fill out cells A3, B3, C3 & D3, i then already have a form control button in place. (which will run the code im hoping you can do)
    What i would like is vba code that will auto fill the range (H8:O8 (P8 & Q8 are seperate) and down by as many component items, as displayed in 'H14:Q19') by looking in the tab 'Raised' for the most recent order of the same Final Part Code, and show all the Component part codes in exactly the same format as shown in 'Rasied' tab.

    Would anyone care to have a stab at my query? please ignore the silly descriptions, they are only for display!

    Massive appreciation in advance. Galvinpaddy
    Attached Files Attached Files
    Last edited by galvinpaddy; 03-23-2012 at 01:34 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-14-2012
    Location
    Arizona USA
    MS-Off Ver
    Excel 2000/2007
    Posts
    408

    Re: Find a value in a database for the most recent date, return all values relating to it.

    "Component part codes" is this the same as Order#1 and Order#2?? I'm not clear on what you are using to match with from sheet to sheet. Please clarify.

  3. #3
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: Find a value in a database for the most recent date, return all values relating to it.

    Apologies, I want to look for the FINAL PC.
    I then want the VBA code to find the most recently raised 'Final Part code', and display all component part codes below it.
    The sheet attached should help put some clarity into my poor description (been a very long day so apologies.)
    Galvinpaddy

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Find a value in a database for the most recent date, return all values relating to it.

    Hi glavinpaddy
    In Sheet Raised are there always 6 and only 6 elements in Columns J through L for each Final P/C?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: Find a value in a database for the most recent date, return all values relating to it.

    Hi Jaslake,
    Unfortunately no, the qty of build items fluctuates dependant on the final product code, at times it can be as little as 1 component and as high as 8 components.
    I had imagined that the code would find the most recent date raised in column F (raised tab) then put the following code in
    Please Login or Register  to view this content.
    Now obviously the range would not be set at F9, it would depend on what cell the code i want help with would locate as being the most recent.
    Apologies if my explanations are a touch bland, my eyes are burning from excel lol.

    Thanks for your input so far!!

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Find a value in a database for the most recent date, return all values relating to it.

    Hi glavinpaddy
    I believe I can give you the code for this
    the code i want help with would locate as being the most recent
    Try this in a General Module
    Please Login or Register  to view this content.
    If you need something further, let me know.

  7. #7
    Forum Contributor
    Join Date
    03-21-2012
    Location
    Ho Chi Minh city
    MS-Off Ver
    Excel 2003
    Posts
    180

    Re: Find a value in a database for the most recent date, return all values relating to it.

    PHP Code: 
    Option Explicit
    Sub GPE
    ()
     
    Dim Sh As WorksheetRng As RangesRng As Range
     
     Set Sh 
    ThisWorkbook.Worksheets("Raised")
     
    Set Rng Sh.Range(Sh.[I1], Sh.[I65500].End(xlUp))
     
    Set sRng Rng.Find([A3].Value, , xlFormulasxlWhole)
     [
    h8].Resize(713).ClearContents
     
    If Not sRng Is Nothing Then
        
    [h8].Resize(, 10).Value sRng.Offset(, -6).Resize(, 10).Value
        With sRng
    .Offset(11)
            .
    Offset().Resize(.End(xlDown).Row - .Row3).Copy Destination:=[o9]
        
    End With
     End 
    If
    End Sub 

  8. #8
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: Find a value in a database for the most recent date, return all values relating to it.

    Hi again,

    First off thanks for the input, however, whilst both codes do something, its not what i need (sorry)
    (Reloaded attachment)

    Tab = 'Raising'
    On this tab, cells A3, B3, C3 & D3 (highlighted in red) are entered manually by user.
    Cell A1 (green) comes from a vba code that shows the user name of the person logged in, then filters it down to just the initials - so this is already working and fine. So can be ignored.
    Cells F8 & G8 these are also manually enetered later - So can be ignored
    Cells H8, I8 & J8 all in blue have set formulas in place to show the relevant info - So can be ignored
    Column Q is always manually entered - So can be ignored

    Now to start on the bits that i need

    Using the info in Cell A3, i want the code (it will be linked to the button) to search through the Tab named 'Raised' and locate not only the same final part code, but also the most recent date of entry, and then copy all info (as highlighted in green on tab named 'Raised') and paste it onto the tab named 'Raising' in the respective cells. (task in task, customer in customer etc.)

    The rest of the work is already done as i have other codes & formulas in place, its just the one code above i cant do

    Many thanks so far for the input and many thanks in advance!!
    Galvinpaddy
    Attached Files Attached Files
    Last edited by galvinpaddy; 03-22-2012 at 11:36 AM.

  9. #9
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Find a value in a database for the most recent date, return all values relating to it.

    Hi glavinpaddy
    This code is in the attached. Please note, there is no error checking...if you need help with that, let me know. For Cell A3 I'd suggest Data Validation. Let me know of issues.
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1