Excel VBA programming Tip

[boris3006]

Hi,

I am trying to teach myself to write a macro in VBA. I have run into a problem, and I am hoping somebody can help. I download a speadsheet with dates in col A, and hi, low, and close stock price in column b through d. I have written code (shown below) to ask the user to enter a date. I would like the macro to search for the cell of the date entered and then set a variable equal to the corresponsing price in column D of that row. I am having problems using variables properly in my code. Can somebody offer suggestions. Below is the code I have so far.

Sub NewFDAdrug()
Dim ApprovalDate
Dim Symbol
Dim PriceDayZero
Dim setRange
Dim setRangeFound
Symbol = InputBox("Enter the symbol of the stock")
ApprovalDate = InputBox("Enter the Date of Drug Approval by the FDA")
' I want to write code here that will search for cell the contains ApprovalDate_
' and then set PriceDayZero eqal to the price in column D of that row.

Thanks,
Bruce

 

[Smitty]

Welcome to the Board!

As you already seem to have a grasp of VBA, take a look at the "Find Method" in the VBA helpfile. You should be able to do what you want with .Find & Offset.

I'd offer more, but from your code, it looks like you want to match the Date and the Ticker, not just the date...?

Hope that helps,

Smitty

Note, if you know what they are, you'd be better of declaring your variables as more than just the Variant type (the default). I.E. Dim Symbol as String.