Hi,

The code below hangs for ages if their are no occurancies in the data
which match the task variable.
The macro basically loops through a range and looks for a match of the
task variable. if a match is found the section of the row is copied
to another sheet and some formatting takes place at the end.

When i run this code and numerous matches are found the code runs
quick and all seems fine, but if there are no matches the code just
hangs. I've only included the "Else a.Interior.ColorIndex = 1" to see
that the code is checking the range.

The full code is below, but I think the problem is in this section?

ThisWorkbook.Sheets("AWD Grid").Select
For Each a In ThisWorkbook.Sheets("AWD Grid").Range("B2:B1000")
If Not a.Value = "" Then
For Each b In Range(Cells(a.Row, useTimeWindow), Cells(a.Row,
useTimeWindow + 23))
If b.Value = Task Then
Range(Cells(a.Row, useTimeWindow), Cells(a.Row,
useTimeWindow + 23)).Copy
Sheets("Workaid").Range("C4").Offset(myRowOffset, 0)
Sheets("Workaid").Range("C4").Offset(myRowOffset,
-2).Value = Cells(b.Row, 1).Value
Sheets("Workaid").Range("C4").Offset(myRowOffset,
-1).Value = Cells(b.Row, 2).Value
myRowOffset = myRowOffset + 1
Exit For
End If
Next b
Else
a.Interior.ColorIndex = 1
End If

Next a

Hope you can help.
Regards,
Matt




Private Sub CommandButton2_Click()
'produce the workaid

ThisWorkbook.Save


Dim TimeWindow As String
Dim Task As String

TimeWindow = ComboBox2.Value
Task = ComboBox3.Value

Dim useTimeWindow As Integer

If TimeWindow = "06:00 - 10:00" Then
useTimeWindow = 7
ThisWorkbook.Sheets("Workaid").Select
Range("C3").Value = "06:00"
Range("D3").Value = "06:10"
Range("C3:D3").Select
Selection.AutoFill Destination:=Range("C3:Z3"),
Type:=xlFillDefault
ElseIf TimeWindow = "10:00 - 14:00" Then
useTimeWindow = 31
ThisWorkbook.Sheets("Workaid").Select
Range("C3").Value = "10:00"
Range("D3").Value = "10:10"
Range("C3:D3").Select
Selection.AutoFill Destination:=Range("C3:Z3"),
Type:=xlFillDefault
ElseIf TimeWindow = "14:00 - 18:00" Then
useTimeWindow = 55
ThisWorkbook.Sheets("Workaid").Select
Range("C3").Value = "14:00"
Range("D3").Value = "14:10"
Range("C3:D3").Select
Selection.AutoFill Destination:=Range("C3:Z3"),
Type:=xlFillDefault
ElseIf TimeWindow = "18:00 - 22:00" Then
useTimeWindow = 79
ThisWorkbook.Sheets("Workaid").Select
Range("C3").Value = "18:00"
Range("D3").Value = "18:10"
Range("C3:D3").Select
Selection.AutoFill Destination:=Range("C3:Z3"),
Type:=xlFillDefault
ElseIf TimeWindow = "22:00 - 02:00" Then
useTimeWindow = 103
ThisWorkbook.Sheets("Workaid").Select
Range("C3").Value = "22:00"
Range("D3").Value = "22:10"
Range("C3:D3").Select
Selection.AutoFill Destination:=Range("C3:Z3"),
Type:=xlFillDefault
ElseIf TimeWindow = "02:00 - 06:00" Then
useTimeWindow = 127
ThisWorkbook.Sheets("Workaid").Select
Range("C3").Value = "02:00"
Range("D3").Value = "02:10"
Range("C3:D3").Select
Selection.AutoFill Destination:=Range("C3:Z3"),
Type:=xlFillDefault
End If



Dim a As Range
Dim b As Range
Dim c As Range

Dim myRowOffset As Integer

ThisWorkbook.Sheets("Workaid").Range("A4:Z500").Cl ear

ThisWorkbook.Sheets("AWD Grid").Select
For Each a In ThisWorkbook.Sheets("AWD Grid").Range("B2:B1000")
If Not a.Value = "" Then
For Each b In Range(Cells(a.Row, useTimeWindow), Cells(a.Row,
useTimeWindow + 23))
If b.Value = Task Then
Range(Cells(a.Row, useTimeWindow), Cells(a.Row,
useTimeWindow + 23)).Copy
Sheets("Workaid").Range("C4").Offset(myRowOffset, 0)
Sheets("Workaid").Range("C4").Offset(myRowOffset,
-2).Value = Cells(b.Row, 1).Value
Sheets("Workaid").Range("C4").Offset(myRowOffset,
-1).Value = Cells(b.Row, 2).Value
myRowOffset = myRowOffset + 1
Exit For
End If
Next b
Else
a.Interior.ColorIndex = 1
End If

Next a

ThisWorkbook.Sheets("Workaid").Select
Dim EndOfRange As String

Range("C4").End(xlToRight).End(xlDown).Select
Range("C4", ActiveCell).Select

For Each c In Selection
If c.Value = "PM" Then
c.Interior.ColorIndex = Range("C1").Interior.ColorIndex
ElseIf c.Value = "XD" Then
c.Interior.ColorIndex = Range("G1").Interior.ColorIndex
ElseIf c.Value = "MHE" Then
c.Interior.ColorIndex = Range("L1").Interior.ColorIndex
ElseIf c.Value = "MR" Then
c.Interior.ColorIndex = Range("P1").Interior.ColorIndex
ElseIf c.Value = "" Then
c.Interior.ColorIndex = 2
Else: c.Interior.ColorIndex = Range("T1").Interior.ColorIndex
End If


Next c

Selection.ClearContents
Unload frmWorkaid


End Sub