Solved: VBA beginner exercise

[Tvercetti]

Hi everyone
I have started learning VBA and am trying to write code to do the following, but have been trying all night and not working:
In column A I have the dates, and column B I have the prices...
I want a sub to search down the list of prices for the first price that exceeds the searchprice argument (which user defines when running this)
I then want a message to display what month the user's price was exceeded.

Thanks you so much for your help and woud appreciate all support

Tony

[Bob Phillips]

Public Sub Test()
Dim mpPrice
Dim i As Long

mpPrice = InputBox("Which price")
If mpPrice <> "" Then

With ActiveSheet
For i = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row
If .Cells(i, "B").Value > CDbl(mpPrice) Then
MsgBox .Cells(i, "A").Text
Exit For
End If
Next i
End With
End If
End Sub

[Tvercetti]

Public Sub Test()
Dim mpPrice
Dim i As Long

mpPrice = InputBox("Which price")
If mpPrice <> "" Then

With ActiveSheet
For i = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row
If .Cells(i, "B").Value > CDbl(mpPrice) Then
MsgBox .Cells(i, "A").Text
Exit For
End If
Next i
End With
End If
End Sub

Thank you for the help.
Since I am a beginner, I would really appreciate to know what ...
If mpPrice <> "" Then ... means.

[Bob Phillips]

It is checking if Cancel was hit in the Inputbox.

[Tvercetti]

Thanks so much xld, I really appreciate the help on this site.

When I run the sub and type in a price, I receive a message wich says "Date", but not the actual date when the price exceeded the price I type in.
Where can I edit the sub to resolve this?
Thanks again

[rory]

If you have headers in row 1, change this line:
For i = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row
to this:
For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row

[Tvercetti]

I attached the file, as I tried doing this but to no avail

Thanks

[herzberg]

I noticed that the data only begins in row 3. Hence, similar to what rory has suggested previously, the loop should begin from 3, instead of 2.

For i = 3 To .Cells(.Rows.Count, "B").End(xlUp).Row I've tried it out and it works fine.

[Tvercetti]

Thanks everyone for all the help.
I know it's easy for most people on here but,
I aso wanted to add some text in this ... so I would like to say at the end
"The first date the price exceeded (the price that was input at the beginning) was (the month)

[rory]

Change the MsgBox line to this:

MsgBox "The first date the price exceeded " & mpPrice & " was " & .Cells(i, "A").Text