PDA

View Full Version : Loop data is not recognized



Tenspeed39355
04-08-2016, 04:12 PM
Hi guys I am using excel and a macro. I am trying to use a loop.
I have some stock symbols in column A. I want the loop to move one symbol at a time
from Column A to Column D, D1. At this point there is no problem with the loop command.
When the symbol is in D1 I am trying to use the following formula in E1. =xlqprice(D1,"Yahoo").
The formula should look at the symbol in D1 and return the current market price for the symbol
in D1. For some reason the formula in E1 does not recongnize what is in D1. Now for the funny part.
When the loop is finished looping thru column A and the last symbol is showing in D1 the formula in E1
works for the last symbol. My question is how do I get the formula to work for all the symbols as they show
up in D1?. Another funny thing is happening. If I do a copy and paste from column A to D1 the formula works for
each symbol Only when I use the macro loop will the formula not work. Thanks for any help with this.
Max

Aussiebear
04-08-2016, 06:19 PM
Sample workbook please Tenspeed.

Tenspeed39355
04-09-2016, 07:33 AM
This is the part of the formula that will not recognize the symbol. I am sending the MACRO also.
you will not be able to see the formula so I am sending it =xlqprice(D1,"Yahoo")


ABE


AFT
15.21


ACG






ACP






dsm






ADX






AFB






AFT

Tenspeed39355
04-09-2016, 07:40 AM
I forgot to send the macro so here it is

Sub getdata()
x = 1
While ActiveSheet.Range("a" & x).Formula <> ""
Range("K28").Insert xlDown
Range("K28").Value = Range("K27").Value
ActiveSheet.Range("d1").Formula = ActiveSheet.Range("a" & x).Formula
ActiveSheet.Calculate
ltime = Timer()
While Timer() - ltime < 3
DoEvents
Wend
x = x + 1
Wend
End Sub

Paul_Hossler
04-09-2016, 09:53 AM
Still not a WORKBOOK

Clean your private data, and post a sample WB that demonstrates the problem please

Tenspeed39355
04-10-2016, 03:19 AM
I sent the attachment. I have never sent an attachment so I hope this works for you.

Paul_Hossler
04-10-2016, 06:39 AM
There were two functions not in the workbook, so I had to fake them

When I update D1, the Volume and Close columns update just as formulas

Did not need the macros at all






Function xlqhVolume(S As String, D As Date, Y As String) As Double
Application.Volatile
xlqhVolume = 1000000 * Rnd
End Function

Function xlqhClose(S As String, D As Date, Y As String) As Double
Application.Volatile
xlqhClose = 1000 * Rnd
End Function

Tenspeed39355
04-11-2016, 05:55 AM
There were two functions not in the workbook, so I had to fake them

When I update D1, the Volume and Close columns update just as formulas

Did not need the macros at all






Function xlqhVolume(S As String, D As Date, Y As String) As Double
Application.Volatile
xlqhVolume = 1000000 * Rnd
End Function

Function xlqhClose(S As String, D As Date, Y As String) As Double
Application.Volatile
xlqhClose = 1000 * Rnd
End Function

Tenspeed39355
04-11-2016, 06:00 AM
Thanks for the reply. Did you run the macro? The problem happens when I run the macro. If I do a copy and paste from any of the symbols
in column A to D1 the xlq formula works. Only when the loop is in progress does the xlq not work.
Do you want me to insert the two formulas in to the macro. Where would I put them?
Max

Paul_Hossler
04-11-2016, 07:09 AM
Yes, pasting a symbol into D1 doesn't cause a crash for me with ProcessData running.

So I would think that it's the xlq... macros that cause a problem

Look at my previous attachment and just replace my two stubs of xlq macros and repost

Since it seems to just cycle through the symbol list, why bother to paste something in?

Tenspeed39355
04-11-2016, 08:13 AM
Yes, pasting a symbol into D1 doesn't cause a crash for me with ProcessData running.

So I would think that it's the xlq... macros that cause a problem

Look at my previous attachment and just replace my two stubs of xlq macros and repost

Since it seems to just cycle through the symbol list, why bother to paste something in?

Tenspeed39355
04-11-2016, 08:19 AM
Paul If I do a copy and paste. xlq works. Only when the loop is working xlq does not see D1. Now for the other part. When the loop is finished, down to
the last symbol then xlq will see D1 and put the stock price in D1. I will need some help on the following. Is there a way to run a loop without using
the macro? If so please let me know. I think I will try the following just to see if the following will work. I am going to use RECORD MACRO on several symbols
and between each symbol I am going to put in the timer of 3 seconds. I will let you know how this works.
Max