0

Im trying to get the forex rate from a website for a range of dates and i want to put a loop.

the problem is that the loop is not working. Please help me.

My code below:

Sub Get_ForEx_Rates()
'
' Get_ForEx_Rates Macro
'
  
   Dim Start_date As Date
   Dim End_Date As Date
   Dim n As Date
   
   
   Start_date = Worksheets("Ticker").Range("B2").Value
   End_Date = Worksheets("Home").Range("B6").Value
   
   i = Start_date
   
   Do Until i = End_Date
   
    i = i + 1
   
    Sheets("Ticker").Select
    Range("C1").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    For Each objConnection In ThisWorkbook.Connections

    bBackground = objConnection.OLEDBConnection.BackgroundQuery
    objConnection.OLEDBConnection.BackgroundQuery = False
    objConnection.Refresh
    objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next
    
    
    
    Sheets("Sheet3").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("USD Rates").Select
    Range("B1").Select
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    
    Sheets("USD Rates").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "='[Get ForEx Rates.xlsx]Ticker'!R1C2"
    
    Range("A2").Select
    Range("B2").Select
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(0, -1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    
    Loop
    
    Sheets("Home").Select
    Range("A1").Select
    
   
   
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Alakar
  • 1
  • How is it not working? What happens? Do you get an error? Also you should read up on avoiding `.select` in Excel VBA: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – jamheadart Jun 24 '20 at 13:51
  • It's impossible for us to solve this issue as it involves debugging the data you're locally working with on your computer. Therefore, debug your code, and check the values of B2 on the "Ticker" worksheet and the value of B6 on the "Home" worksheet. Verify, using the debugger, if the mentioned startdate is small indeed than the mentioned enddate. – Dominique Jun 24 '20 at 15:16
  • Just an idea: I would not use Do Until i = End_Date, if I am not sure that it will be really equal. I would use relation (< or >) if possible. – Viktor West Jun 24 '20 at 16:00

0 Answers0