Sub FourHourJpyBox()

'IMPORTANT NOTE - ENSURE CORRECT i VALUE SET FOR HOURLY RANGE - NOTED WHERE BELOW

' Declare variables

    Dim WeekRow, StartBar, EndBar As Integer
    
    Dim BarHigh, BarLow, BuyTr, Low, High

    WeekRow = 3
  
    
    ' Find the number of the last rows on the sheets
        lastweek = Worksheets("Results").UsedRange.Rows.Count
        lasthour = Worksheets("Hourly").UsedRange.Rows.Count
    
'Main loop to go through week by week

    For WeekRow = WeekRow To lastweek Step 1
    
    'reset the variables

        BarHigh = 0
        BarLow = 10000
        StartBar = 0
        EndBar = 0
    
'Find the row number for the first hour of Sunday trading
' IMPORTANT NOTE - Adjust the"i" number in the range below to take you to the end of your hourly data

    StartBar = Application.VLookup(Worksheets("Results").Cells(WeekRow, 2).Value, Worksheets("Hourly").Range("a2:i282"), 9, False)
    On Error GoTo error
 
    EndBar = StartBar + 3
    
' Find the 4 hour high

    i = StartBar
    
    For i = StartBar To EndBar Step 1

        If Worksheets("Hourly").Cells(i, 5).Value > BarHigh Then
        BarHigh = Worksheets("Hourly").Cells(i, 5).Value
        End If
  
    Next i
    
    'Print result
    Worksheets("Results").Cells(WeekRow, 3).Value = BarHigh
   
 ' Find the 4 hour low
 
    i = StartBar
    
   For i = StartBar To EndBar Step 1
   
        If Worksheets("Hourly").Cells(i, 6).Value < BarLow Then
        BarLow = Worksheets("Hourly").Cells(i, 6).Value
        End If
    
    Next i
    
    'Print result
    Worksheets("Results").Cells(WeekRow, 4).Value = BarLow
    
' Fill in the long and short triggerpoints - 20% either side

    Difference = BarHigh - BarLow
    
    BuyTr = WorksheetFunction.Round((BarHigh + (Difference * 0.2)), 2)
    selltr = WorksheetFunction.Round((BarLow - (Difference * 0.2)), 2)
    
    Worksheets("Results").Cells(WeekRow, 5).Value = BuyTr
    Worksheets("Results").Cells(WeekRow, 6).Value = selltr
    
' Loop through the hours to see if the bar breaks long or short
    EndBar = EndBar + 1
    i = EndBar
  
  For i = EndBar To lasthour Step 1
  
  High = Worksheets("Hourly").Cells(i, 5)
  Low = Worksheets("Hourly").Cells(i, 6)
  
    'Breaks both long and short in next hour
    If High > BuyTr And Low < selltr Then
    Worksheets("Results").Cells(WeekRow, 7).Value = "Stopped": Exit For
    End If
    
    ' Long?
    If High > BuyTr Then
    Worksheets("Results").Cells(WeekRow, 7).Value = "Long"
    traderow = i
    
        'When is long stop hit?
        For j = traderow To lasthour Step 1
        
        'If Low < stop (ie if stop hit) then
        If Worksheets("Hourly").Cells(j, 6).Value < selltr Then
            rowhit = j: Exit For
        End If
        
        Next j
        
        'If stop never hit scenario
        If j = (lasthour + 1) Then rowhit = lasthour
        
        'Loop from traderow to rowhit testing for max high
           
        maxhigh = 0
        
        For k = traderow To rowhit Step 1
        If Worksheets("Hourly").Cells(k, 5).Value > maxhigh Then
        maxhigh = Worksheets("Hourly").Cells(k, 5).Value
        Worksheets("Results").Cells(WeekRow, 8).Value = maxhigh
        
        'R/R ratio
        RR = ((maxhigh - BuyTr) / (BuyTr - selltr))
        Worksheets("Results").Cells(WeekRow, 9).Value = RR
        
        End If
        Next k
        
    'Long ending
    Exit For
    End If
    
    ' Short?
    
    If Low < selltr Then
    Worksheets("Results").Cells(WeekRow, 7).Value = "Short"
    traderow = i
    
        'When is short stop hit?
        For j = traderow To lasthour Step 1
        
        'If High > stop (ie if stop hit) then
        If Worksheets("Hourly").Cells(j, 5).Value > BuyTr Then
            rowhit = j: Exit For
        End If
        
        Next j
        
        'If stop never hit scenario
        If j = (lasthour + 1) Then rowhit = lasthour
        
        'Loop from traderow to rowhit testing for min low
           
        minlow = 10000
        
        For k = traderow To rowhit Step 1
        If Worksheets("Hourly").Cells(k, 6).Value < minlow Then
        minlow = Worksheets("Hourly").Cells(k, 6).Value
        Worksheets("Results").Cells(WeekRow, 8).Value = minlow
        
        'R/R ratio
        RR = ((selltr - minlow) / (BuyTr - selltr))
        Worksheets("Results").Cells(WeekRow, 9).Value = RR
        
        End If
        Next k
        
    'Short ending
    Exit For
    End If
    
  
  Next i

error:

    Resume Next


Next WeekRow




    
End Sub