Change VBA Code,Stock look up

[paul]

I have this code that downloads stock quotes into Excel
from Yahoo.It was posted some time ago on the newsgroup
boards by a poster named, HTH Shockley.

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

This code uses a list of symbols in the first column of
the first sheet in the workbook and gets basic quotes from
Yahoo.Instead of the first column I would like to be able
to assign a column(range?)for the symbols that are to be
looked up. For example; column("B") or range(B1:B20).
Would I need to change the destination range too? I new to
this gave it a shot and failed. Please help

thanks in advance,
Paul
P.S.using Office 2000

 

[Tom Ogilvy]

Change


LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
SymString = SymString & Cells(i, 1) & " "
Next i


to

Dim cell as Range
for each cell in worksheets("Sheet1").Range("B1:B20")
SymString = SymString & Cell & " "
Next

The destination is a new workbook, so you don't need to change that.

 

[Guest]

-----Original Message-----
Change


LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
SymString = SymString & Cells(i, 1) & " "
Next i


to

Dim cell as Range
for each cell in worksheets("Sheet1").Range("B1:B20")
SymString = SymString & Cell & " "
Next

The destination is a new workbook, so you don't need to change that.

--
Regards,
Tom Ogilvy





Thanks so very much Tom.

Happy holidays to you an your family.

Paul

 

[Guest]

The change worked great.
When I put a second range of symbols below the first the
data go up to the first open cell. In my case that would
be B1.How do I make keep the data aligned next to my
second set of symbols(Mysymbols=A30:A47)I know that I will
have to make a second macro then run them sequentially.But
how do I go about keeping them aligned? It looks like this
part to me.
Workbooks.Open sURL
Set rngSource = Cells(1).CurrentRegion
x = rngSource.Rows.Count
y = rngSource.Columns.Count
With ThisWorkbook.Sheets(2)
Set rngDest = Range(.Cells(1, 1), .Cells(x, y))
But again I am lost.

Thanks again,
Paul