Macro Range issues (I think)

T

tmann

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
 
B

bpeltzer

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).
 
T

tmann

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!
 
T

tmann

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top