Sleeper: Using a Macro to get fund data

I think i see the problem. The macro code is far too quick for your data retrieval. In my sample, Excel is responiding instantly to the cell A1 change; going to Yahoo takes time. I'll investigate if the macro can be suspended until cell J255 has changed in value. I foresee a problem, however, if two codes return the same value, but let's worry about that later.

Next attempt.
I've changed the loopup routine to refer to today's date, otherwise the wrong values may be returned. This needs to be corrected (Omit the -3) for live use.
I've built in a loop to cycle until cell J255 changes, with a time delay (15 secs) before it times out. This should get out of the "same value" problem.
You can test the code by showing sheet 1 and sheet 2 together on a split window, and manually changing the values in J255

Code:

---

Option Explicit
Sub GetValues()
Const TimeDelay = 15
Dim MyData As Range, Cel
Dim Result As String, MyDate As String
Dim TmpVal As String
Dim St As Double
Sheets("Sheet1").Select
'Note Mydate set to use J255 value
MyDate = Format(Now() - 3, "d-MMM-yy")
Result = "Sheet1!" & Range("A:A").Find(What:=MyDate, After:=Range("A1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Offset(0, 9).AddressLocal
Debug.Print Result
Sheets("Sheet2").Select
Set MyData = Range("A1", Range("A1").End(xlDown))
For Each Cel In MyData
'Get initial value
TmpVal = Range(Result)
Sheets("Sheet1").Range("A1").Formula = Cel
'Loop until a change occurs; Timed out after TimeDelay value
St = Timer
Do
DoEvents
If Timer - St > TimeDelay Then TmpVal = "Timed Out"
Loop Until TmpVal <> Range(Result).Value
If TmpVal = "Timed Out" Then
Cel.Offset(0, 1) = TmpVal
Else
Cel.Offset(0, 1) = Range(Result)
End If
Next
End Sub

---

I found the problem with the numbers in the RSI ss. Let me refresh your memory.
When the macro started it would bring over the first RSI number and put it in B1. When the next number ran the new RSI number when in B2. The problem was that the number in B1 changed to the new number. Here is what I was doing that did not work.
I went to Sheet1 J255 did a COPY, left Sheet1 and when to Sheet2 B1 and did a COPY, PASTE SPECIAL, LINK. I would stop at that point. Here is the fix. When to
Sheet1 J255 and did the COPY, left and when to Sheet1 B1 and did a COPY, PASTE
SPECIAL, LINK, moved the curser to anothe cell, came back to B1 and did a COPY,
PASTE SPECIAL, VALUE and stopped. That fixed the problem. I did the same for all the fund symbols. The macro ran without a problem. I ran across this someplace and think
it is the answer. Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
It looks like that for each symbol there is a ton of repetive work to do for 450 funds.
If we have the answer to the big problem my question ot you is can we do just a short peice of macor so that when we finish with one symbol the loop goes to the next symbol. Again let me thank you for all your time.
Max

When you paste a value into Cell A1, what triggers the programme that contacts yahoo? How long does it take for the value in J255 to change, after entering the new code in A1?

Let me see if i can explain. The fund symbols are in Sheet2 A1:A6. I did a link between
Sheet2 A1:A5 to Sheet1 A1 for each fund symbol. When the macro runs the macro changes Sheet1 A1 to the new symbol. The RSI value in J255 is changed at the same time. I can not give you a time. Let me say that there is not delay. The program that contacts Yahoo is a program named XLQ. Here is how it works. In Sheet 1 C1 I have the command xlqhclose($A$1,A2), this sends the command to Yahoo to look for the closing price on the date in A2 and the closing price in $A$1. Hope this is helpfull.
Max

Hi Max,
I downloaded a trial copy of XLQ and copied your Sheets 1 & 2 into it; I then input your formula as above and ran the code (latest version). It took time to run, with all the time outs, but I ran it again and the correct results appear to be displayed. I adjusted the time out to 1 second and ran the code with a fresh set of data. Time outs occurred for each item, but much quicker, and a second running produced the desired results. The demo file is attached for your perusal.
I'm away for a few days now, so can't respond until Monday. Basic thing is, the code works!
MD

Hi Max,

Update on the last to avoid running code twice.

Code:

---

Option Explicit

Sub GetValues()
Dim MyData As Range, Cel
Dim Result As String, MyDate As String
'Pass data to cells to obtain data
Set MyData = Range("A1", Range("A1").End(xlDown))
For Each Cel In MyData
Sheets("Sheet1").Range("A1").Formula = Cel
Next
Sheets("Sheet1").Select
'Note Mydate set to use J255 value
MyDate = Format(Now() - 11, "d-MMM-yy")
Result = "Sheet1!" & Range("A:A").Find(What:=MyDate, After:=Range("A1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Offset(0, 9).AddressLocal
'Pass data to cell to return data
Sheets("Sheet2").Select
Set MyData = Range("A1", Range("A1").End(xlDown))
For Each Cel In MyData
Sheets("Sheet1").Range("A1").Formula = Cel
Cel.Offset(0, 1) = Range(Result)
Next
End Sub

---

I found the problem. The number in J255 is a formula. I did the following and that solved the problem. I started by doing a copy at Sheet1 J255, then when to Sheet2 B1 and did a paste special, link. The fix was that I moved the curser out of B1, then made B1 active again and did a copy, paste special and VALUES. I did this for each cell in Sheet 2 B1:B8.

From the sounds of it, you're linking each of B1:B8 to the same cell, this will result in all cells showing the same value.
My code as tested using the XLQ programme, is returning the required results for 130 records, without having to carry out any paste link operations or other user intervention.