excel vba find max date in range and offset

Posted on 2012-08-22
Last Modified: 2012-08-22
Hello Experts,

I have a set of dates that populates on sheet9.Range("AN1:AN100")

In sheet13 activate worksheet I am trying to get values pulled from sheet9 range

    sheet13.Range("E20") = maxdate   where maxdate is ws function max in rng from sheet9

   Then sheet13.range("E18") = maxdate cell location offset( ,1)  value
    ' this is the invoice numer in column("AO")
   Then sheet13.range("E22") = maxdate cell location offset( ,2) value
    ' this is the invoice amount in column("AP")

I can not seem to put this all together to make it work.  I have other data in the code, but that works fine - I am brain-fried on this section.

Thanks for your assistance,
Question by:mike637
    LVL 19

    Expert Comment

    you can use the formula for cell E29 on sheet13:


    Open in new window

    LVL 19

    Expert Comment

    for cell E18 i guess you mean that you would want to locate the cell in sheet9 that contains the value of the cell E20 in sheet13, and expect the value of the cell in the AO column on the same row.

    is this correct ?
    LVL 19

    Expert Comment

    in this case it would be

    =VLOOKUP(E29, sheet9!AN1:AO100, 2, FALSE)

    Open in new window

    meaning the value of cell E18 will be the cell in the second column (thus: AO) of the row from which the AN cell value is an exact match to the value in cell E29 of sheet13
    LVL 19

    Expert Comment

    for E22 this will be

    =VLOOKUP(E29, sheet9!AN1:AP100, 3, FALSE)

    Open in new window


    Author Comment

    That is correct.

    So far in my code have

    Dim rng as range
    Dim maximum as double

    Set rng = Sheet9.Range("AN1:AN100")
                maximum = Application.WorksheetFunction.Max(rng)
                        Sheet13.Range("E20") = maximum
    end it so far....

    Now I need to get Sheet13.Range("E18") to equal the value one column right of "maximum"
    and Range("E22") to equal the value two columns right of "maximum"

    There is where I get stuck on setting the cell location of "maximum" and getting the offset values for Ranges E18 and E22 for sheet 13.

    I have other code after this that will correspond to cell location of maximum - but once I get past the previous 2 calcuations in the code - I think I will be OK.

    LVL 24

    Accepted Solution

    Maybe this?
    Sub x()
    Dim rng As Range
    Dim maximum As Double
    Dim r As Long
    Set rng = Sheet9.Range("AN1:AN100")
    maximum = Application.WorksheetFunction.Max(rng)
    r = Application.Match(maximum, rng, 0)
    With Sheet13
        .Range("E20") = maximum
        .Range("E18") = rng(r, 2)
        .Range("E22") = rng(r, 3)
    End With
    End Sub

    Open in new window


    Author Closing Comment

    StephenJR - This worked out perfectly.

    Thank you and have a great day!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    Excel file corrupted. 13 28
    find phone numbers in excel 6 24
    how to link excel with word 6 29
    Complex Lookup Formulas 3 30
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now