Consulting

Results 1 to 8 of 8

Thread: Find Max Value in one worksheet and return the value to a cell in another

  1. #1
    VBAX Regular
    Joined
    Mar 2017
    Posts
    55
    Location

    Find Max Value in one worksheet and return the value to a cell in another

    Hi,

    Been searching for some help with this issue to:

    1) Look up max value in a column (number of rows varies) on worksheet B
    2) Then return the the max value to a cell on worksheet A

    Please help.

  2. #2
    VBAX Expert
    Joined
    Apr 2005
    Posts
    819
    Location
    You might try one of these.
    Change references as required.
    Sub Maybe_A()
        Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("E:E"))
    End Sub
    Sub Maybe_B()
        Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Large(Sheets("Sheet2").Range("E:E"), 1)
    End Sub

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Or by formula =MAX(Sheet2!E:E)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Regular
    Joined
    Mar 2017
    Posts
    55
    Location
    Thank you jolivanes. I've used method A and it works. The max value that I'm pulling is a date. How would I format the destination cell ("D5") to date and have it display without slashes: ex, 031517

  5. #5
    VBAX Expert
    Joined
    Apr 2005
    Posts
    819
    Location
    Your Cell D5 can be custom formatted as mmddyy
    or
    Sub Maybe_A()
        With Sheets("Sheet2").Range("D5")
            .Value = WorksheetFunction.Max(Sheets("Sheet3").Range("E:E"))
            .NumberFormat = "mmddyy"
        End With
    End Sub
    or
    Sub Maybe_B()
    Dim a As Date
    a = Application.WorksheetFunction.Max(Sheets("Sheet3").Range("E:E"))
        Sheets("Sheet2").Range("D5").Value = "'" & Format(a, "mmddyy", "000000")
    End Sub
    Last edited by jolivanes; 03-22-2017 at 10:49 PM. Reason: add code

  6. #6
    VBAX Regular
    Joined
    Mar 2017
    Posts
    55
    Location
    The destination cell format displays without slashes ex: 031517. I'm using this cell to concatenate with another cell and the slashes come back in the concatenated cell ex: companyname 03/15/17. Any way to remove slashes from the concatenated cell? I'm using this cell as the file name.

  7. #7
    VBAX Expert
    Joined
    Apr 2005
    Posts
    819
    Location
    If you want to use the date in a filename, why not delete the slashes at that time like after concatenating?
    That way you need to remove the slashes once only.

    If the Company name is in cell D2 and the date with slashes in in cell C7, this will give you want you indicated you need.


    Sub test()
        MsgBox Replace((Range("D2").Value & " " & Range("C7").Value), "/", "")
    End Sub
    Last edited by jolivanes; 03-23-2017 at 11:08 AM. Reason: Add info

  8. #8
    VBAX Regular
    Joined
    Mar 2017
    Posts
    55
    Location
    Thank you for your help.

Posting Permissions

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