The macro below works, but can it be simplified?

What this macro is doing is putting an "H" (for holidays) in the appropriate
cells.

Sub HolidaySelection()
Dim i As Long
Dim j As Long
Range("startpoint").Select
Range("B11:AF22").ClearContents
For i = 1 To 12
For j = 1 To 31
If
Application.WorksheetFunction.CountIf(Worksheets(" Holidays").Range("F5:F13"),
DateSerial(Range("VacYear"), i, j)) Then
ActiveCell.Offset(i, j) = "H"
End If
Next j
Next i
End Sub

Your code is looping 31&12 times for 372 tiumes. My code is looping 9 times
(F5 to F13)

Sub HolidaySelection()
Dim i As Long
Dim j As Long
Dim cell as Range

for each cell in Worksheets("Holidays").Range("F5:F13")
MyMonth = Month(cell)
MyDay = Day(cell)
Range("startpoint").offset(MyMonth,MyDay) = "H"
next cell

"Brad" wrote:

The macro below works, but can it be simplified?

What this macro is doing is putting an "H" (for holidays) in the appropriate
cells.

Sub HolidaySelection()
Dim i As Long
Dim j As Long
Range("startpoint").Select
Range("B11:AF22").ClearContents
For i = 1 To 12
For j = 1 To 31
If
Application.WorksheetFunction.CountIf(Worksheets(" Holidays").Range("F5:F13"),
DateSerial(Range("VacYear"), i, j)) Then
ActiveCell.Offset(i, j) = "H"
End If
Next j
Next i
End Sub

I didn't declare the variable properly

Sub HolidaySelection()
Dim MyMonth As Long
Dim MyDay As Long
Dim cell as Range

for each cell in Worksheets("Holidays").Range("F5:F13")
MyMonth = month(cell)
MyDay = day(cell)
Range("startpoint").offset(MyMonth,MyDay)
next cell

"Brad" wrote:

The macro below works, but can it be simplified?

What this macro is doing is putting an "H" (for holidays) in the appropriate
cells.

Sub HolidaySelection()
Dim i As Long
Dim j As Long
Range("startpoint").Select
Range("B11:AF22").ClearContents
For i = 1 To 12
For j = 1 To 31
If
Application.WorksheetFunction.CountIf(Worksheets(" Holidays").Range("F5:F13"),
DateSerial(Range("VacYear"), i, j)) Then
ActiveCell.Offset(i, j) = "H"
End If
Next j
Next i
End Sub

Much better - Thank you!!


"Joel" wrote:

I didn't declare the variable properly

Sub HolidaySelection()
Dim MyMonth As Long
Dim MyDay As Long
Dim cell as Range

for each cell in Worksheets("Holidays").Range("F5:F13")
MyMonth = month(cell)
MyDay = day(cell)
Range("startpoint").offset(MyMonth,MyDay)
next cell

"Brad" wrote:

The macro below works, but can it be simplified?

What this macro is doing is putting an "H" (for holidays) in the appropriate
cells.

Sub HolidaySelection()
Dim i As Long
Dim j As Long
Range("startpoint").Select
Range("B11:AF22").ClearContents
For i = 1 To 12
For j = 1 To 31
If
Application.WorksheetFunction.CountIf(Worksheets(" Holidays").Range("F5:F13"),
DateSerial(Range("VacYear"), i, j)) Then
ActiveCell.Offset(i, j) = "H"
End If
Next j
Next i
End Sub