HELP FIND HIGHEST VALUE USING VBA

[Lolo122298]

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]

In your macro there is no need for

maxprice = Range("B2").Value 
        maxdate = Range("A2").Value

As 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]

Thank you so much!!! I finally got it!

[snb]

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