Sub UpdateStockPrice()
Dim sVal As Range 'Search Value
Dim tCell As Range 'Ticker Cell
Dim cnt As Long
Dim cnt2 As Long
For cnt = 1 To Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row
Set sVal = Sheets("Sheet4").Range("A" & cnt)
For cnt2 = 1 To 3
Set tCell = Sheets(cnt2).Columns("A").Find _
(sVal, Range("A1"), xlFormulas, xlWhole, xlByRows, xlNext,
False)
If Not tCell Is Nothing Then
tCell.Offset(0, 3) = sVal.Offset(0, 4)
End If
Next
Next
End Sub
Try that. let me know if you have problems
Charles
MT_Netols wrote:
I have a somewhat lengthy question that I'm hoping someone can help me
with. I can't get a macro I'm trying to write to operate correctly, so
maybe someone knows a code that will help.
Here's the situation:
- I have four sheets (Sheet 1, 2, 3, & 4 for simplicity's sake), Sheets
1-3 contain stocks I'm looking at at various points of my analysis
stage. Sheet 4 contains a list of all the stocks from Sheets 1-3.
- I import the stock quotes on Sheet 4 so I can obtain the most current
prices for my stocks.
- Now, what I want to do is have my macro run through the list of
stock tickers on Sheet 4, and if it finds the corresponding ticker in
Sheet 1, I want it to copy in the new current price.
- For example: Stock XYZ is on Sheet 1, with the ticker in column A
and the current price in column D. The ticker is also on Sheet 4 with
the new current price in column E, so what I want to do is something
along the lines of:
For each A:A in Sheet4! If A = Sheet1! A:A Then
copy Sheet4! E:E into Sheet1! D:D
I hope what I'm trying to accomplish makes sense, I basically just want
to update what I have typed in for the current prices without having to
manually type them all in, so if I can have the macro run through the
list in Sheet4, look for it in Sheet1, and if it finds it, copy the
price in. I know that if I can get just one run of this to work I can
change the Sheet references to have it run for Sheets 1, 2, and 3, so
any help would be greatly appreciated.
--
MT_Netols
------------------------------------------------------------------------
MT_Netols's Profile: http://www.excelforum.com/member.php...o&userid=37665
View this thread: http://www.excelforum.com/showthread...hreadid=572758