Excel VBA Question

Two part question
Part a.  I am trying write a sub call StockHigh1 that will takes a single argument called searchPrice.   It will search down the list of prices for the first price that exceeds the searchPrice argument.  If it finds one, it displays the corresponding date in a message, something similar to, “The first date  X stock price exceeded______ was____.”   If the price never exceeded the argument searchPrice, it displays a message to this effect.  Next, I am trying to write a Main sub that uses an input box to ask the user for a price and then calls StockHigh1 with this price as the argument.
Part b. I am also trying to write another sub call StockHigh2 that takes a single argument called specifiedMonth.  This sub will search down the list of prices for the last time up until (and including) the specified month where the stock reached a record high (that is, it was larger than all prices before it, going back to the beginning of 2000).  It will then display in a message, “The most recent record, up until ____, was in ____, when the price reached____.” (Note that Jan-2000 is a record high by default, so at least one record high will always be found.)  Change the Main sub from part a so that the input box now ask for a month and then calls StockHigh2 with this month as an argument.

Please check the attachment for closing prices
Tester-b-v1.0.xls
MasterOfTheSkyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonkeyOteCommented:
Perhaps along the lines of:

Sub Example()
Dim dblSPI As Double, dtSMI As Date
'PRICE
Call StockHigh1(102.1)
dblSPI = Application.InputBox("Enter Price of Interest", "Price", Type:=1)
Call StockHigh1(dblSPI)
'MONTH
Call StockHigh2(DateSerial(2002, 5, 0))
'using Input
dtSMI = Application.Max(DateSerial(2000, 2, 0), Application.InputBox("Enter Month of Interest", "Price", Type:=1))
Call StockHigh2(dtSMI)
End Sub

Sub StockHigh1(dblSP As Double)
Dim dt As Date
dt = Evaluate("MIN(IF(B3:B100>" & dblSP & ",A3:A100))")
If dt Then
    MsgBox "The first date X stock price exceeded " & Format(dblSP, "0.00") & " was " & Format(dt, "mmm-yyyy"), vbInformation, "Found"
Else
    MsgBox "Price Never Exceeded", vbInformation, "Not Found"
End If
End Sub

Sub StockHigh2(specifiedMth As Date)
Dim dblVal As Double, dt As Date
dblVal = Evaluate("MAX(IF(A3:A100<=" & CLng(specifiedMth) & ",B3:B100))")
dt = Evaluate("INDEX(A3:A100,MATCH(" & dblVal & ",B3:B100,0))")
MsgBox "The most recent record, up until " & Format(specifiedMth, "mmm-yyyy") & ", was in " & Format(dt, "mmm-yyyy") & ", when the price reached " & Format(dblVal, "0.00")
End Sub

You might want to adjust the ranges of course.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DonkeyOteCommented:
I should have added - the Example routine shows the subsequent routines being invoked with manually defined variables and subsequently via values as generated via Input Dialogs.
0
StealthyDevCommented:
This will bring an input dialogue:

xyz = InputBox("Enter the value")
0
DonkeyOteCommented:
senthurpandian - better to use Application.InputBox (as already illustrated) given data type can be restricted to valid type:

see: http://msdn.microsoft.com/en-us/library/aa195768(office.11).aspx
0
MasterOfTheSkyAuthor Commented:
Thank you very much!! Yes I will adjust the ranges.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.