Macro Range issues (I think)
 

Hi all -

I think this would be an easy one. Very new to writing Macros...still
in the stage where I'm taking other folk's code and tweaking it for my
own needs. I'm trying to pull stock quotes from Yahoo, and have found
a great (almost) script to do this (on Google groups) -- see below.

Here's my problem: when I enter a stock symbol (say msft) into A2 (or
any row in col A), it places the stock quote in row 1. How I can I get
it to begin the list of quotes in row 2 (or 3, or 4, etc) ???

Thanks,
TMann

Sub GetYQuotes()
Base01 = "http://finance.yahoo.com/d/quotes.csv?s="
Base02 = "&f=sl1d1t1c1ohgv&e=.csv"


sURL = ""
SymString = ""
LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
SymString = SymString & Cells(i, 1) & " "
Next i
sURL = Base01 & SymString & Base02
Workbooks.Open sURL
Set rngSource = Cells(1).CurrentRegion
x = rngSource.Rows.Count
y = rngSource.Columns.Count
With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, 1), .Cells(x, y))
End With
rngDest.Value = rngSource.Value
ActiveWorkbook.Close SaveChanges:=False
End Sub


--
tmann
------------------------------------------------------------------------
tmann's Profile: http://www.excelforum.com/member.php...o&userid=29040
View this thread: http://www.excelforum.com/showthread...hreadid=487720

Macro Range issues (I think)
 

It's the cells(1,1) that's setting the starting point for the output. If you
want the output to begin in A2 instead, change it to cells(2,1).


With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, 1), .Cells(x, y))
End With

Macro Range issues (I think)
 

Thanks, bpeltzer. I tried your suggestion, and while it works, it
introduces another problem: if I enter a list of stock symbols in col A
beginning in A2, it doesn't pull a quote for the last symbol in the
list. For instance, in the list:

MSFT
GME
COH
LCC

it won't pull a quote for LCC. But then if I add another symbol below
LCC, say WMT, it will pull a quote for LCC now but not WMT. I assume
that while it's inserting the data in row 2, it's counting the number
of rows required from row 1 and that's why it misses the last row???
Any thoughts??? Thanks!

bpeltzer Wrote:
It's the cells(1,1) that's setting the starting point for the output.
If you
want the output to begin in A2 instead, change it to cells(2,1).


With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, 1), .Cells(x, y))
End With


--
tmann
------------------------------------------------------------------------
tmann's Profile: http://www.excelforum.com/member.php...o&userid=29040
View this thread: http://www.excelforum.com/showthread...hreadid=487720

Macro Range issues (I think)
 

Actually, I think I just figured it out. I added the 2nd line below to
the code:

x = rngSource.Rows.Count
x = x + 1
y = rngSource.Columns.Count

tmann Wrote:
Thanks, bpeltzer. I tried your suggestion, and while it works, it
introduces another problem: if I enter a list of stock symbols in col A
beginning in A2, it doesn't pull a quote for the last symbol in the
list. For instance, in the list:

MSFT
GME
COH
LCC

it won't pull a quote for LCC. But then if I add another symbol below
LCC, say WMT, it will pull a quote for LCC now but not WMT. I assume
that while it's inserting the data in row 2, it's counting the number
of rows required from row 1 and that's why it misses the last row???
Any thoughts??? Thanks!


--
tmann
------------------------------------------------------------------------
tmann's Profile: http://www.excelforum.com/member.php...o&userid=29040
View this thread: http://www.excelforum.com/showthread...hreadid=487720