Compile Error: Variable not defined

[motilulla]

Hi everyone,</SPAN></SPAN>

The code is getting, “Compile Error: Variable not defined” on this line</SPAN></SPAN>

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal</SPAN></SPAN>

This item is highlited: xlSortOnValues</SPAN></SPAN>

Please need help, I am using excel version:2000</SPAN></SPAN>

Option Explicit
Sub GetData()
    Dim DataSheet As Worksheet
    Dim endDate As String
    Dim startDate As String
    Dim fromCurr As String
    Dim toCurr As String
    Dim str As String
    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
        fromCurr = DataSheet.Range("fromCurr").Value
        toCurr = DataSheet.Range("toCurr").Value
        
 str = "http://www.oanda.com/currency/historical-rates/download?quote_currency=" _
   & fromCurr _
   & "&end_date=" _
   & Year(endDate) & "-" & Month(endDate) & "-" & Day(endDate) _
   & "&start_date=" _
   & Year(startDate) & "-" & Month(startDate) & "-" & Day(startDate) _
   & "&period=daily&display=absolute&rate=0&data_range=c&price=bid&view=table&base_currency_0=" _
   & toCurr _
   & "&base_currency_1=&base_currency_2=&base_currency_3=&base_currency_4=&download=csv"
QueryQuote:
             With Sheets("Data").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Data").Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            
Sheets("Data").Range("a5").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a5"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)
            
   Sheets("Data").Columns("A:B").ColumnWidth = 12
   Sheets("Data").Range("A1:b2").Clear
         
    LastRow = Sheets("Data").UsedRange.Row - 6 + Sheets("Data").UsedRange.Rows.Count
 Sheets("Data").Range("A" & LastRow + 2 & ":b" & LastRow + 5).Clear
    Sheets("Data").Sort.SortFields.Add Key:=Range("A5:A" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets("Data").Sort
        .SetRange Range("A5:b" & LastRow)
        .Header = xlYes
        .MatchCase = False
       .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With
  
DeleteCharts
 
Application.DisplayAlerts = True
  
    With ActiveSheet.ChartObjects.Add _
            (Left:=Range("A11").Left, Width:=375, Top:=Range("A11").Top, Height:=225)
        .Chart.SetSourceData Source:=Sheets("Data").Range("A5:b" & LastRow)
        .Chart.ChartType = xlLine
    End With
   
Dim ch As ChartObject
For Each ch In ActiveSheet.ChartObjects
ch.Select
ActiveChart.Axes(xlValue).MinimumScale = WorksheetFunction.Min(Sheets("Data").Range("b5:b" & LastRow))
ActiveChart.Axes(xlValue).MaximumScale = WorksheetFunction.Max(Sheets("Data").Range("b5:b" & LastRow))
ActiveChart.Legend.Select
Selection.Delete
Next ch
        
End Sub
Sub DeleteCharts()
On Error GoTo ExitChart
Dim ws As Worksheet
Dim chObj As ChartObject
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
chObj.Delete
Next chObj
Next ws
ActiveWorkbook.Charts.Delete
ExitChart:
Application.DisplayAlerts = True
Exit Sub
End Sub

Thanks And Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[VoG]

That is code for Excel 2007 and later.

The macro recorder will give you the correct syntax for Excel 2000.

 

[motilulla]

Thank you VoG, I tried recordig macro but really could not get it work.</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>