I am a scheduler and I am trying to figure out a way to count the hours of
open schedules that I have due to attrition. I have a spreadsheet with the
times of the day, in 30 minute increments, listed across the top. I am
inputting a time, say 11:00am, and a number of hours, say 4, and I would like
the macro to place a number "1" beginning at 11:00am in each box for the
alloted increments, in this case 8, since it is 4 hours. I have tried all
that I know how and I can't get it to work.

Hi Sandi,

I think the below code will help.
I assume that the time values are in row 1 and you want the schedule numbers
(1,2,3 and so on) on row 2.

Sub Schedule()
Dim i As Integer
Dim j As Double
Dim mStartTime As Date
Dim mIntervals As Integer

mStartTime = Range("A3").Value
mIntervals = Range("B3").Value
'Search the starting time
i = 1
Do While True
If Cells(1, i).Value = mStartTime Then Exit Do
i = i + 1
Loop
j = 0.5
While j <= mIntervals
Cells(2, i) = j * 2
j = j + 0.5
i = i + 1
Wend

End Sub

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Sandi" wrote:

I am a scheduler and I am trying to figure out a way to count the hours of
open schedules that I have due to attrition. I have a spreadsheet with the
times of the day, in 30 minute increments, listed across the top. I am
inputting a time, say 11:00am, and a number of hours, say 4, and I would like
the macro to place a number "1" beginning at 11:00am in each box for the
alloted increments, in this case 8, since it is 4 hours. I have tried all
that I know how and I can't get it to work.

This works but I have 2 dilemmas:

1) I would like for all of the numbers to be a "1" rather than 1 2 3 4 etc.
so I can do a count
2) I will have hundreds of entries. How do I make it show the results for
row 4 in row 4 and so on?

Thank you for your assistance.

"Pranav Vaidya" wrote:

Hi Sandi,

I think the below code will help.
I assume that the time values are in row 1 and you want the schedule numbers
(1,2,3 and so on) on row 2.

Sub Schedule()
Dim i As Integer
Dim j As Double
Dim mStartTime As Date
Dim mIntervals As Integer

mStartTime = Range("A3").Value
mIntervals = Range("B3").Value
'Search the starting time
i = 1
Do While True
If Cells(1, i).Value = mStartTime Then Exit Do
i = i + 1
Loop
j = 0.5
While j <= mIntervals
Cells(2, i) = j * 2
j = j + 0.5
i = i + 1
Wend

End Sub

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Sandi" wrote:

I am a scheduler and I am trying to figure out a way to count the hours of
open schedules that I have due to attrition. I have a spreadsheet with the
times of the day, in 30 minute increments, listed across the top. I am
inputting a time, say 11:00am, and a number of hours, say 4, and I would like
the macro to place a number "1" beginning at 11:00am in each box for the
alloted increments, in this case 8, since it is 4 hours. I have tried all
that I know how and I can't get it to work.