dgali001
10-18-2010, 11:01 AM
Hi all,
I'm working with large amounts of data coming from TV Rating Reports. I'm trying to come up with a Macro to automate some of the tedious work involved. I've come up with the following code to get the average rating for the movie in question using the Rating's time block. But so far I can only get it to work for one day. I need help looping to the other days.
Sub AverageRating()
Dim i As Integer
Dim t As Integer
t = 1
Do
t = t + 1
MovieStartTime = Worksheets("calculations").Range("C" & t)
MovieEndTime = Worksheets("calculations").Range("D" & t)
StartMovieDate = Worksheets("calculations").Range("A" & t)
SumRating = 0
CountAvg = 0
On Error Resume Next
For i = 2 To 25
StartNielsonDate = Worksheets("calculations").Range("G" & i)
NielsonStartTime = Worksheets("calculations").Range("H" & i)
NielsonEndTime = Worksheets("calculations").Range("I" & i)
Rating = Worksheets("calculations").Range("J" & i)
If MovieStartTime < NielsonStartTime Then
If NielsonStartTime < MovieEndTime Then
Var1 = True
Else
Var1 = False
End If
ElseIf MovieStartTime >= NielsonStartTime And NielsonEndTime >= MovieStartTime And NielsonEndTime <= MovieEndTime Then
Var1 = True
Else
Var1 = False
End If
If Var1 = True Then
Var1 = 1
SumRating = (Rating + SumRating)
ElseIf Var1 = False Then
Var1 = 0
End If
CountAvg = Var1 + CountAvg
Next i
AvgRating = SumRating / CountAvg
Worksheets("calculations").Range("F" & t).Value = AvgRating
Loop Until Cells(t + 1, 1) <> StartMovieDate
End Sub
I'm also attaching the Excel file to better understand what I'm trying to do. Column E is the manual calculations I'm doing (see formulas to understrand the logic) and column F is what my code is getting. The code works fine until it needs to analyze a movie going into the next day. But aside from that it works but needs to be looped somehow to get the averages for the other days!!
Any ideas??
I'm working with large amounts of data coming from TV Rating Reports. I'm trying to come up with a Macro to automate some of the tedious work involved. I've come up with the following code to get the average rating for the movie in question using the Rating's time block. But so far I can only get it to work for one day. I need help looping to the other days.
Sub AverageRating()
Dim i As Integer
Dim t As Integer
t = 1
Do
t = t + 1
MovieStartTime = Worksheets("calculations").Range("C" & t)
MovieEndTime = Worksheets("calculations").Range("D" & t)
StartMovieDate = Worksheets("calculations").Range("A" & t)
SumRating = 0
CountAvg = 0
On Error Resume Next
For i = 2 To 25
StartNielsonDate = Worksheets("calculations").Range("G" & i)
NielsonStartTime = Worksheets("calculations").Range("H" & i)
NielsonEndTime = Worksheets("calculations").Range("I" & i)
Rating = Worksheets("calculations").Range("J" & i)
If MovieStartTime < NielsonStartTime Then
If NielsonStartTime < MovieEndTime Then
Var1 = True
Else
Var1 = False
End If
ElseIf MovieStartTime >= NielsonStartTime And NielsonEndTime >= MovieStartTime And NielsonEndTime <= MovieEndTime Then
Var1 = True
Else
Var1 = False
End If
If Var1 = True Then
Var1 = 1
SumRating = (Rating + SumRating)
ElseIf Var1 = False Then
Var1 = 0
End If
CountAvg = Var1 + CountAvg
Next i
AvgRating = SumRating / CountAvg
Worksheets("calculations").Range("F" & t).Value = AvgRating
Loop Until Cells(t + 1, 1) <> StartMovieDate
End Sub
I'm also attaching the Excel file to better understand what I'm trying to do. Column E is the manual calculations I'm doing (see formulas to understrand the logic) and column F is what my code is getting. The code works fine until it needs to analyze a movie going into the next day. But aside from that it works but needs to be looped somehow to get the averages for the other days!!
Any ideas??