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
> >