Sleeper: Using a Macro to get fund data

[Tenspeed39355]

I am using a formula that gives the Relitive strength index of any funds.The formula at J160 gives me the number I need to put in column B in sheet1.
How do I get the number from J160 to move to column B in sheet1?
So far I have linked all the fund symbols in Sheet 1 Column A to Sheet 2 Column
A1. When I run the macro the symbol in Sheet 2 column A1 changes. As it changes the number I need in J160 changes but I can not move the number in
J160 to move to sheet 1 Column B1 then the new number in J160 to move to B2
and so on. Can you give me some help with this one. Thanks
Max Parris

[Zack Barresse]

Hi Max,

Not sure where your data is. You specify two sheets, but don't distinguish between them. If you're talking about with code, maybe something like this ...

Sheets("Sheet1").Range("B65536").End(xlUp).Offset(1).Value = Range("J160").Value

This also does not take into account what sheet J160 is on, which I'd specify, as if you don't it will assume it's on the activesheet.

Does that help any?

[Tenspeed39355]

The fund symbols are in Sheet 1 and the rsi formula is in sheet 2 column J160
Sheet 1 looks like this.
A B
1 ACG
2 AOF
3 AWF
4 BIF
5 BTF

I want to move the data from sheet 2 J160 to this sheet and put the data in
column B. I start the macro from here by going to Tools Macro and run.
I can send you the macro as I have it if that will help.
Max

[Zack Barresse]

.. I can send you the macro as I have it if that will help.

Can you post it to the board?

[Tenspeed39355]

Here is the macro. I used the recorder to get the macro. I put in the wait command just to see the macro run

Sub Macro1()
' Macro1 Macro
' Macro recorded 02/04/2005 by Max Parris
Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste Link:=True
    Sheets("Sheet2").Select
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Range("A2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste Link:=True
    Sheets("Sheet2").Select
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Range("A3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste Link:=True
    Sheets("Sheet2").Select
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Range("A4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste Link:=True
    Sheets("Sheet2").Select
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste Link:=True
    Sheets("Sheet2").Select
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Range("A6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste Link:=True
    Sheets("Sheet2").Select
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Range("A7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste Link:=True
    Sheets("Sheet2").Select
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Range("A7").Select
End Sub

[mdmackillop]

Hi TenSpeed,
Is it something like the attached you are after. It copies each value from a range of cells into a target cell in turn, and returns another value based upon it to the adjacent cell of the data range.
MD

Sub GetResult()
    For Each cel In Range("MyData")
        Sheets("Sheet2").Range("A1") = cel
        cel.Offset(0, 1).Formula = Range("Result")
    Next
End Sub

[Tenspeed39355]

I will download it and get back to you after I run it
Thanks a load
max

[mdmackillop]

Here's a wee function to simplify your Wait method. Add the DoWait code as a separate sub and call it with the line "DoWait x", where x is the delay in seconds that you want.

Sub Test()
Tm = Format(Now(), "hh:mm:ss")
DoWait 3
MsgBox "Time up! " & Tm & " - " & Format(Now(), "hh:mm:ss")
End Sub
 
Sub DoWait(Secs As Integer)
Application.Wait Now() + Secs / 24 / 3600
End Sub

[Tenspeed39355]

Hi I tried you code but was having prolems with it. I hope this will explain what the
problem is. I have 450 funds symbols in column A In column B I want to put a number
from Tab 2. I have used the recorder to link each symbol in column A to A1 in Tab 2. When I run the macro the number I want in comes up in Column B1 which is good. Now here is the problem. When the macro goes to B2 the NEW NUMBER comes up correctly.
The problem is the number that was in B1 now becomes the number in B2. This goes on until all the numbers are beside the symbols, but they are all what the last number is. How can I get the numbers for each symbol to stay the same.
Example of what I see
AOF 6.35
ACG 6.35
ASG 6.35


This is what I want it to look like
AOF 6.35
ACG 3.25
ASG 10.00
I appreate any help on this.
I can not send the ss as I am using a program that changes the number I am after in Tab two when the market price changes. I know the number is changing because
lets say the first symbol is AOF, the number comes up beside it but when the new
symbol comes up and the new number comes up the first number changes to the new
number in B!.
Thanks in advance.
Max

[mdmackillop]

Hi Max,
If you could post the spreadsheet, even non-functioning, it would help my understanding.
MD

[Tenspeed39355]

I have never uploaded to you so how do I upload to you

Max

[mdmackillop]

First Zip your file. then click on Go Advanced below the Quick Reply box, Near the bottom of that screen, there's a button to Manage Attachments; Follow the instructions from there.

[Tenspeed39355]

I have ziped the ss and it should be with this e-mail
If you need anything please let me know The number I need for each symbol is
on Sheet 1 at J255. As the macro goes down the list that number changes and
I was each changed number to go with the new symbol
Max

[mdmackillop]

I can't see what would cause this. I've rejigged the spreadsheet to show the relevent cells etc. perhaps you could set up yours in similar fashion to keep an eye on what's happening.

[mdmackillop]

Hi Max,
I've adjusted the code to suit your spreadsheet. The code should return the value from the last cell in column J, as this appears to change daily (I hope that's correct). For Demo purposes, I set this cell to a vlookup function based on the yellow cells, to return some changing results.

[Tenspeed39355]

I am still having a problem with what you sent. Lets try the following.
Would you please make up a macro that will start in Sheet2 A1 and go to A25.
The stock symbols will be in that range. When the macro starts going from A1 to A25
each time it moves to the next cell it will also change the symbol in Sheet1 A1. Each time
Sheet1 A1 changes I want Sheet1 J255 to enter the new number in Sheet2 B1 and when
Sheet1 A1 changes again the new number in J255 will go to Sheet2 B2. I can get the symbols in Sheet1 A1 to change the problem I am having is that each time the numbers in J255 change the number in Sheet2 B1 changes to the new number. The old number will not stay in B1. If I can get the number in B1 and B2 to be different numbers that will solve the problem. I have sent you the ss before. Hope you can help me with this problem.
Max

[mdmackillop]

I'm at a loss here!
When I run the code in my last zip file I get the following result

ASG 42.1151
AOF 54.64999
ARK 57.14831
AWF 62.59569
BHK 63.91999
BIF 71.52099
CNN 55.36975

Which is what the Vlookup returns in J255, based on the yellow cells.

[Tenspeed39355]

That is what I get also. I have over 400 symbols to get the J255 number to go in. How do I do that. I tried typing in a new symbol with no luck.
Max

[mdmackillop]

How is the number in cell J255 changed when a new entry goes into cell A1? Is there another programme running which does this? Could it be that the the two programmes are out of synch? you could add in a delay as above.
Add the sub

Sub DoWait(Secs As double) 
    Application.Wait Now() + Secs / 24 / 3600 
End Sub

and add the line "DoWait 1" after For Each Cel In MyData

[Tenspeed39355]

Good morning . Here is what I did. I put the wait commands in. Ran the macro.
I could see Sheet1 A1 change symbols, at the same time I can see the J255 number change. The symbol change for each symbol I had in Sheet2. I had a 5 sec wait put in.
I can also see in Sheet2 B1 the first J255 number when in but when it came to the second number going in to B2 the new number went in as planned. The problem is that B1
changed to the new number. I can see J255 change and hold for as many times as needed. I can not get the number in B1 to stay put.
You asked what was changing Sheet1 A1. I did a link between sheet2 A1 to Sheet1 A1
I did a link for each symbol in Sheet2 to Sheet1 A1.
I am using a program called XLQ. XLQ is in the price of the fund column. When the
symbol changes xlq goes to Yahoo and gets the new Mkt price and that changes J255.
I know we are close to getting this resolved. I still would like for you to write a macro
showing me how to make Sheet1 A1 change when the macro goes from Sheet2 A1:A5
The way I am doing it takes a lot of time. Can a Loop or for next command do it.
Thanks for your time with this.
Max