Macro that moves down a worksheet

[charlier15]

I'm working on a macro that pulls historical stock prices based on column A (ticker) and column B (date). In my summary sheet, I have the following...

A B C
F 10/21/2006
AAPL 6/5/2005
GE 8/1/2003
BBY 9/4/2004

The macro is designed to pull Ford's stock quote for 10/21/06 into column C. It works for the first row, my question is how can I modify the macro so that it works its way down the sheet until the next row is blank? I'm working with several thousand dates and therefore, I need it to be automated. I know there's some kind of loop or reference that I need to build in, but I'm not sure how it would apply to my macro. Here's what my macro looks like...

"Sheet 2" is my summary sheet with the tickers and dates
"Sheet" 1 has a Yahoo utility that the macro is pasting the ticker and dates into
"Data" is the sheet where stock data is returned for the ticker on the specific date

So I think all of the references in the macro would be locked except on Sheet2, A3 (Ticker) and B3 (Date) need to move down the sheet.

Sub Master()
Sheets("Sheet2").Select
Range("A3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B6").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B7").Select
ActiveSheet.Paste
Range("B8").Select
ActiveSheet.Paste



Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name
Dim LastRow As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Sheets("Data").Cells.Clear

Set DataSheet = ActiveSheet

StartDate = DataSheet.Range("startDate").Value
EndDate = DataSheet.Range("endDate").Value
Symbol = DataSheet.Range("ticker").Value
Sheets("Data").Range("a1").CurrentRegion.ClearContents

qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol
qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
"&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Sheets("Data").Range("a1") & "&q=q&y=0&z=" & _
Symbol & "&x=.csv"

QueryQuote:
With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False

Sheets("Data").Columns("A:G").ColumnWidth = 12

LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count

Sheets("Data").Sort.SortFields.Add Key:=Range("A2:A" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets("Data").Sort
.SetRange Range("A1:G" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Sheets("Data").Select
Range("G2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C3").Select
ActiveSheet.Paste
End With

End Sub

 

[GlennUK]

Have you looked for help on loops?

 

[charlier15]

I've done some reading on loops and know that they can be created to continue until they reach a blank row. I guess where I'm struggling at is how to integrate a loop into the existing macro.

 

[GlennUK]

Well, to loop until the last non-blank row I generally do this:

For Each c In Range("A1:A" & Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row)
   If c.Value = "whatever" Then MsgBox "Found It!"
Next