PDA

View Full Version : range of object failed xcel 2013



Timo451
09-03-2016, 09:31 AM
I have recopied my 2010 macros to 2013 and am getting the error above on this line
StartDate = DataSheet.Range("startDate").Value

help would be appreciated



Sub GetData()
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 = xlCalculationAutomatic


Sheets("Data").Cells.Clear
Set DataSheet = ActiveSheet

StartDate = DataSheet.Range("startDate").Value '<<<<<<<<<<<<<<<<<

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

SamT
09-03-2016, 10:31 AM
Sheets("Data").Cells.Clear
Set DataSheet = ActiveSheet
Doesn't the first line Activate Sheets "Data?" Doesn't that Clear all the Cells for DataSheet?

Good Programming Practice dictates that we avoid the use of ActiveSheet whenever possible. Assign an explicit sheet to DataSheet.


Are you sure that the Range Name is accurate? Pay careful attention to extra and trailing spaces.

First put "Option Explicit" at the top of all your code pages, and make liberal use of the VBA Menu Debug >> Compile.

In some Cell on the Worksheet, start a Formula, (Type the "=" character,) then Insert Name "startDate" and copy it from the formula bar.

Then go back to the VBA editor and paste the copied name in place of the Range Name in the code.

Alternately, on a blank sheet, Insert >> Name >> Paste List and copy all the names you need from that list into your code. With that list, if you Format the cells to align right, you will see any extra trailing spaces.

p45cal
09-04-2016, 03:09 AM
Not at all sure about this, but avoid the use of DataSheet, it's used in the object model as the grid of data you can add to a chart.
Try changing it thoughout your code to DataSht.