INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Error 1004: Select method of Range class failed

Error 1004: Select method of Range class failed

(OP)
I'm attempting to modify a spreadsheet from Google finance which captures historical stock quotes. One enters the stock symbol and dates on the first sheet then activates the button to get the data on the "Data" sheet.
The Google file is: "Google Finance Stock Quotes.xlsm"

The macro uses the querytables function and initially puts the CSV headers in A1 and the CSV stock data in A2. It then converts text to columns, thereby ranging from column A thru column F. Row A1 contains the headers and row A2 contains the data for the 1st stock symbol.

The macro works properly.

However, I'm attempting to modify the action in order to get all my stock quotes for the end of a given month with just the push of one button.
I first created a "Symbols" sheet listing all the symbols I'm interested in. I want to iterate thru the symbols keeping the same date so I can build a table on the "Data" sheet to eventually be imported into my Quicken app.
So instead of having to type the symbol in the first sheet, I only need to enter the end of month date. Then I can iterate thru the symbols.
What happens after the first data is retrieved is that the second querytable action adds the next symbol's data in a newly created A column in row 3, thereby shifting the first set of data one column right.
What I attempt to do is copy the new data (2 rows, 1 column worth) and move it to column B. That way I can delete the newly formed column A. Then I would want to delete row 3 which now contains the CSV for column headers again. And lastly I would convert the new row 3, (which now would contain the CSV data for the next stock symbol), to columns to that the new data lines up under the proper headers.
The problem arises when I attempt to select the range of A1:A2; I get error 1004: Select method of range class failed.

I've used breakpoints and Debug.Print to follow the code, but it always fails at this point.

CODE

Option Explicit

Sub GetData()
Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim i As Integer, j As Integer
Dim r1 As String
Dim QTCnt 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  THIS LINE IS REPLACED WITH THE FOR LOOP FOLLOWING
Sheets("Data").Range("a1").CurrentRegion.ClearContents

For i = 1 To 82 'There are 82 stock symbols on the Sheets("Symbols")

Symbol = Trim(Sheets("Symbols").Range("a" & i).Value)

qurl = "http://finance.google.com/finance/historical?q=" & Symbol
qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
       "+" & Day(StartDate) & "+" & Year(StartDate) & _
       "&enddate=" & MonthName(Month(EndDate), True) & _
       "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"

QueryQuote:
'First time thru works as planned
If i = 1 Then   'The section containing the WITH statement was in the original Google Finance .xlsm
                'I moved the Refresh statement after the SaveData statement

    With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _
Destination:=Sheets("Data").Range("a" & i)) .BackgroundQuery = True .TablesOnlyFromHTML = False '.Refresh BackgroundQuery:=False .SaveData = True .Refresh BackgroundQuery:=False End With QTCnt = Sheets("Data").QueryTables.Count Debug.Print QTCnt For j = 1 To QTCnt Sheets("Data").QueryTables.Item(j).Delete Next j Sheets("Data").Range("a" & i).TextToColumns Destination:=Sheets("Data").Range("a" & i), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False Sheets("Data").Range("a" & i + 1).TextToColumns Destination:=Sheets("Data").Range("a" & i + 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Else 'Subsequent times thru the loop come here With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _
Destination:=Sheets("Data").Range("a" & i + 1))
.BackgroundQuery = True .TablesOnlyFromHTML = False '.Refresh BackgroundQuery:=False .SaveData = True .Refresh BackgroundQuery:=False End With QTCnt = Sheets("Data").QueryTables.Count Debug.Print QTCnt For j = 1 To QTCnt Sheets("Data").QueryTables.Item(j).Delete Next j r1 = "A" & i + 1 & ":A" & i + 2 Debug.Print r1 Sheets("Data").Range(r1).Select 'THIS IS WHERE THE CODE FAILS!!!! Sheets("Data").Selection.Copy Sheets("Data").Range("B" & i + 1).Select Application.CutCopyMode = False Sheets("Data").Columns("A:A").Select Sheets("Data").Selection.Delete Shift:=xlToLeft Sheets("Data").Range("A" & i + 1).Select Sheets("Data").Rows(i + 1).Delete Sheets("Data").Range("a" & i + 1).TextToColumns Destination:=Sheets("Data").Range("a" & i + 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
End If Sheets("Data").Range("G" & i + 1) = Symbol Next i Sheets("Data").Columns("A:G").ColumnWidth = 12 End Sub

Any insights would be greatly welcomed.
Thanks,
Vic

RE: Error 1004: Select method of Range class failed

Hi,

Avoid Select and Activate....

CODE

.
    Sheets("Data").Range(r1).Copy 

If you do use the Select method, be certain that the sheet in question is the ActiveSheet

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Error 1004: Select method of Range class failed

(OP)
Thanks Skip. I'll give that a try.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close