+ Reply to Thread
Results 1 to 9 of 9

Find Max and Min plus Date of each Max and Min using an excel script

  1. #1
    Registered User
    Join Date
    01-02-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Find Max and Min plus Date of each Max and Min using an excel script

    Hi there,

    I have multiple workbooks containing the water level data for a particular bore.

    So if the bore is listed 46217, it will have its own workbook and contain a series of columns listing the date the water level was taken, the water level and other details.

    What I want to do is create a script that will create a separate excel file containing the max and min water level (the column labeled WLMP__m_), plus the date of each value for each bore/workbook.

    There is approx 140 workbooks and I need to complete this for each one.

    I have attached one of the files as an example.

    Thanks,

    Mick
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    12,508

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    If Data layouts and sheet naming rule are the same in each workbook, try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-02-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    Thanks Jindon,

    Sorry I'm a fairly basic VBA guy, so if you could explain a few things that would be great.

    Do I need to change any directories in this code to suit my own? When I run the script it prompts me to locate a folder - is that folder the location of all excel files?

    Thanks so much for your reply!

    Mick

  4. #4
    Registered User
    Join Date
    01-02-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    Actually I've just worked it out and it works perfectly! Fantastic!

    What do you think is the best way to learn VBA? It's a very handy tool!

  5. #5
    Registered User
    Join Date
    01-02-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    Also Jindon,

    If I wanted to extract the first line of data and the last line of data from each spread sheet, would that be possible?

    For example the in the spreadsheet provided above, Row 2 and Row 292. These would change depending on the spread sheet.

    Thanks for your help

    This is what I have so far!

    Sub SaveLastLine()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim FileName As String
    Dim lastCol As Integer
    Dim lastRow As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set ws = ActiveSheet ' Here I instantiate the active worksheet
    Set wb = Workbooks.Add ' Here I instantiate the new workbook

    lastCol = ws.Range("A1").End(xlToRight).Column
    lastRow = ws.Range("A1").End(xlDown).Row

    ws.Range(ws.Cells(1), ws.Cells(2, lastCol)).Copy wb.Worksheets(1).Range("A1") ' Here I copy the first 3 rows and paste in the first worksheet of your new workbook
    ws.Range(ws.Cells(lastRow, 1), ws.Cells(lastRow, lastCol)).Copy wb.Worksheets(1).Range("A3") ' Here I copy the last row and paste

    FileName = "yourfilename.xlsx"
    wb.SaveAs FileName

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub
    Last edited by mickcondon89; 01-03-2018 at 10:11 PM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    12,508

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    1)
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

    2)
    Quote Originally Posted by mickcondon89
    If I wanted to extract the first line of data and the last line of data from each spread sheet, would that be possible?
    How do you want the output to accompany current the results?
    If upload a result workbook, it may help.

  7. #7
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    986

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    Depends on how you learn.
    you can buy one Of the books
    https://www.amazon.co.uk/Excel-24-Ho...7988642&sr=8-1
    https://play.google.com/store/books/...d=46toCUvklIQC
    https://www.amazon.co.uk/Excel-Progr.../dp/0764574124

    if you learn that way.

    I learn this sort of thing by doing,
    So I need interesting tasks to complete and access to google, vbaexpress and its vba articles and maybe a forum

    this might be a interesting thing for playing around with without stressing you out much
    if it doesn't open note that

    "Private Declare Function" is "Private Declare ptrsafe Function" for 64bit excel

    https://coral.ie.lehigh.edu/~ted/fil...isc/sample.xls
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  8. #8
    Registered User
    Join Date
    01-02-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    Oh sorry about that!

    I'll make sure I do that next time.

    I would want the output alongside the max and min data. Pretty much in the same way as before. Even a seperate workbook is fine!

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    12,508

    Re: Find Max and Min plus Date of each Max and Min using an excel script

    Not really clear.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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