View Full Version : Solved: VBA beginner exercise
Tvercetti
09-30-2007, 11:44 AM
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
09-30-2007, 12:06 PM
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
09-30-2007, 12:21 PM
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
09-30-2007, 01:08 PM
It is checking if Cancel was hit in the Inputbox.
Tvercetti
09-30-2007, 02:32 PM
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
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
09-30-2007, 04:11 PM
I attached the file, as I tried doing this but to no avail
Thanks
herzberg
09-30-2007, 08:13 PM
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
10-01-2007, 05:44 AM
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)
Change the MsgBox line to this:
MsgBox "The first date the price exceeded " & mpPrice & " was " & .Cells(i, "A").Text
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.