Help with Cycling through Ranges

oric

New Member
Joined
Jul 19, 2005
Messages
36
Hi all,

I wanted to get some peoples advice on how to write efficient code to work with cycling through 2 ranges. Here is my issue.

I have a whole bunch of experimental data. On sheet1 I have the times in which my events of interest happenned. So sheet1 looks something like

Column A Column B
eventID EventTime

On sheet 2 I have the times in which my detectors were turned on and off

Column A Column B Column C
DetectorID Time-on Time-off


What I would like to do is cycle through each event and check to see if any of the detectors were on when the event occured. If the detector was on then I would like to return the detector ID(s) in the columns next to my EventTime of sheet1.

This is the skeleton of my code so far.
Code:
Sub practice()
Dim Event As Range, DetectorTime As Range, EventRange As Range 
Dim DetectorRange As Range 
Set EventRange = Sheets(1).Range("B1:B" &  _ Range("B65536").End(xlUp).Row)
Set DetectorRange = Sheets(2).Range("B1:B" &  _ Range("B65536").End(xlUp).Row)
    For Each Event In EventRange
       For Each DetectorTime In DetectorRange
           If Event.Value >= DetectorTime.Value And Event.Value <= DetectorTime.Offset(0,1).Value Then
               Event.Offset(0, 1).Value = DetectorTime.Offset(0,-1).Value
           End If
       Next
    Next
End Sub

I have a lot of data (>5000 events and ~1000 detectors). I don't know if this is the most efficient way to go through these 2 ranges.

Thanks in advance..
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Threads
1,119,137
Messages
5,576,300
Members
412,716
Latest member
thviid
Top