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

x
Solved

# excel vba find max date in range and offset

Posted on 2012-08-22
Medium Priority
3,635 Views
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.

Michael
0
Question by:mike637
• 4
• 2

LVL 19

Expert Comment

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

``````=MAX(sheet9!AN1:AN100)
``````
0

LVL 19

Expert Comment

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 ?
0

LVL 19

Expert Comment

ID: 38319839
in this case it would be

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

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
0

LVL 19

Expert Comment

ID: 38319840
for E22 this will be

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

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.

Michael
0

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
``````
0

Author Closing Comment

ID: 38319922
StephenJR - This worked out perfectly.

Thank you and have a great day!

Michael
0

## Featured Post

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.
###### Suggested Courses
Course of the Month20 days, 10 hours left to enroll