Challening time question

[PiPaPils]

Hi all!

There is something I'd like to do, but unfortunately am not able to do so myself...

I have several sheets with entries of trades (in the example it is the "Trades" sheet). A trade is opened at date X, time Y and it closes at date Z and time Q. On another sheet ("Exposure" sheet in the example) I have a long list with all possible dates with steps of 1 hour. What I want to do is to check each entry in the Trades sheet, and modify the cells in the Exposure sheet if the times are between open and close date. An example might illustrate this best:

 

[PiPaPils]

The first sheet looks like this:

[/IMG]



So in the second sheet I have a list that looks like this. The first entry runs from 20-01-10 10:46 to 21-01-10 0:35. I'd like to modify all cells in that range:

[/IMG]

 

[JumboCactuar]

With formula? VBA? Conditional formatting?

In your trade open column you can simply do

=IF(AND(B6>=$C$2,B6<=$D$2),1,0)

 

[PiPaPils]

Hi Jumbo!

I tried that if statement, but the issue is that C2 and D2 need to go for the next cell, so C3 and D3 after they checked for C2 and D2. Was thinking of a For Each... Cell in range loop, but I do not really know to properly build a loop in a loop...

Kind regards,

Koen

 

[offthelip]

this code should do it for you:

Sub test()Dim cnt As Variant


With Worksheets("Sheet1")
 lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
 inarr = Range(.Cells(1, 2), .Cells(lastrow, 3))
End With
With Worksheets("Sheet2")
 lastdate = .Cells(Rows.Count, "B").End(xlUp).Row
 datearr = Range(.Cells(1, 2), .Cells(lastdate, 2))
 Range(.Cells(1, 3), .Cells(lastdate, 3)) = ""
 outarr = Range(.Cells(1, 3), .Cells(lastdate, 3))
cnt = 0


For i = 7 To lastdate
   For j = 6 To lastrow
    If inarr(j, 1) >= datearr(i - 1, 1) And inarr(j, 1) < datearr(i, 1) Then
     cnt = cnt + 1
    End If
  
    If inarr(j, 2) >= datearr(i - 1, 1) And inarr(j, 2) < datearr(i, 1) Then
     cnt = cnt - 1
    End If
   Next j
   
   Range(.Cells(i, 3), .Cells(i, 3)) = cnt
 Next i


 
 End With


End Sub

 

[PiPaPils]

this code should do it for you:

Sub test()Dim cnt As Variant


With Worksheets("Sheet1")
 lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
 inarr = Range(.Cells(1, 2), .Cells(lastrow, 3))
End With
With Worksheets("Sheet2")
 lastdate = .Cells(Rows.Count, "B").End(xlUp).Row
 datearr = Range(.Cells(1, 2), .Cells(lastdate, 2))
 Range(.Cells(1, 3), .Cells(lastdate, 3)) = ""
 outarr = Range(.Cells(1, 3), .Cells(lastdate, 3))
cnt = 0


For i = 7 To lastdate
   For j = 6 To lastrow
    If inarr(j, 1) >= datearr(i - 1, 1) And inarr(j, 1) < datearr(i, 1) Then
     cnt = cnt + 1
    End If
  
    If inarr(j, 2) >= datearr(i - 1, 1) And inarr(j, 2) < datearr(i, 1) Then
     cnt = cnt - 1
    End If
   Next j
   
   Range(.Cells(i, 3), .Cells(i, 3)) = cnt
 Next i


 
 End With


End Sub

Ah superb! This is exactly what I need!

Thanks a lot!!!