[Webinar] Learn how to a build a cloud-first strategyRegister Now


excel vba find max date in range and offset

Posted on 2012-08-22
Medium Priority
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
  • 4
  • 2
LVL 19

Expert Comment

by:Arno Koster
ID: 38319828
you can use the formula for cell E29 on sheet13:


Open in new window

LVL 19

Expert Comment

by:Arno Koster
ID: 38319832
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

by:Arno Koster
ID: 38319839
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 19

Expert Comment

by:Arno Koster
ID: 38319840
for E22 this will be

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

Open in new window


Author Comment

ID: 38319857
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

StephenJR earned 2000 total points
ID: 38319870
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

ID: 38319922
StephenJR - This worked out perfectly.

Thank you and have a great day!


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 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