PDA

View Full Version : [SOLVED] HELP FIND HIGHEST VALUE USING VBA



Lolo122298
11-05-2017, 05:41 PM
For my assignment, I have to create a macro that will find the highest value in a range along with the corresponding date. I created a macro but it only evaluates the first value in the range. How do I fix this?

Sub findhighestprice()
Dim maxprice As Currency
Dim maxdate As Date
Dim i As Integer
For i = 1 To 252
maxprice = Range("B2").Value
maxdate = Range("A2").Value
If Range("A2").Offset(i, 0) > Range("B2").Offset(i, 1) Then
maxprice = Range("B2").Offset(i, 1)
maxdate = Range("A2").Offset(i, 0)
End If
Next i
Range("s5").Value = maxprice
Range("s6").Value = maxdate
End Sub

Simon Lloyd
11-05-2017, 08:02 PM
In your macro there is no need for
maxprice = Range("B2").Value
maxdate = Range("A2").ValueAs they do nothing, your argument that you have put in asks the question IF the DATE of any offset row from A2 to A254 is greater than any value (Currency)in the 2nd column then....?
How do you propose to evaluate an integer against a DATE?
Secondly, if you do happen to find an argument that is true you want maxprice to be the value of Range "C2" + I e.g "C176"

You need to look at using Application.WorksheetFunction and using the MAX function, you can make use of FIND to get you the address then display the OFFSET to give you the DATE.

As it is an assignment I cannot give you the solution but will try to help coach you to it :)

Lolo122298
11-05-2017, 08:14 PM
Thank you so much!!! I finally got it!

snb
11-06-2017, 02:07 AM
It looks more an Excel question than a VBA question:


Sub M_snb()
MsgBox [index(A1:A252,match(max(B1:B252),B1:B252,0))]
End Sub