Looping through cell range

[jlbutcher]

Hello everyone,

I am using a query table in excel that accepts the data from accress. Okay I have no problems with the query table however, I'm having troubles looping through a cell range to calculate an average based on the cell values.

Listed below are the cell values.
<TABLE style="WIDTH: 495pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=662 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" span=4 width=51><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=17 width=83>Employee#</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=71>First Name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=70>Last Name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=51>Period1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=51>Period2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=51>Period3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=51>Period4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=100>Last Review</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=83>Average</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=51>Results</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Griffin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Ellett</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>88</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>100</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>22</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>23</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right x:num="39897">3/25/2009 0:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Cole</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Ellett</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>64</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>66</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>44</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>99</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right x:num="39897">3/25/2009 0:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD></TR></TBODY></TABLE>

What I am trying to accomplish is to add Periods 1 through 4 places value in the average column and then create an if statement if the average is below 79 than the cell value "Results" would be "rescheduled" if the value is 80 and above then the cell value "Results" would be "doing fine"

Here is the code that I've been playing around with:

Public Sub UpdateTrain()
'Declare the variables
 Dim dtmNewDate As Date, strNewDate As String, strDate As Date
 Dim shtTraning As Worksheet, qtbTraining As QueryTable
 Dim rngCurrent As Range, rngCell As Range
 
 'Set the range
 Set shtTraning = Application.Workbooks("EllettMgroup.xls").Worksheets("Training")
 Set qtbTraining = shtTraning.QueryTables("trainingquery")
 
 
     dtmNewDate = Format(Date, Format:="Short Date")
         qtbTraining.CommandText = "Select * FROM Training where LastScoreDate = #" & dtmNewDate & "#"
 
'rerun the query with the new SQL statement .
 qtbTraining.Refresh BackgroundQuery:=False
'set the headers
With shtTraning
    .Range("A1") = "Employee#"
    .Range("B1") = "First Name"
    .Range("C1") = "Last Name"
    .Range("D1") = "Period1"
    .Range("E1") = "Period2"
    .Range("F1") = "Period3"
    .Range("G1") = "Period4"
    .Range("H1") = "Last Review"
    .Range("I1") = "Average"
    .Range("J1") = "Results"
End With
'Set the range
Set rngCurrent = Range("A:J").CurrentRegion
Set rngCurrent = rngCurrent.Offset(rowoffset:=1, columnoffset:=3)
'Start looping through the range using a variable rngCell
 
For Each rngCell In rngCurrent
 
 
       If rngCell.Value = dtmNewDate Then
 
      rngCell.Offset(columnoffset:=5).Value = _
      Val(Val(rngCell.Offset(columnoffset:=0).Value + Val(rngCell.Offset(columnoffset:=1).Value + _
            Val(rngCell.Offset(columnoffset:=2).Value + Val(rngCell.Offset(columnoffset:=3).Value)))) / 4)
      'qtbTraining.FillAdjacentFormulas = True
 
      End If
 
      If rngCell.Value < 79 Then
      rngCell.Offset(columnoffset:=4).Value = "Reschedule"
 
 
 
 
     End If
 
    'find the over 80%
    If rngCell.Offset(columnoffset:=5).Value > 80 Then
 
       rngCell.Offset(columnoffset:=7).Value = "Doing Fine"
 
     End If
Set rngCell = rngCell.Offset(rowoffset:=1)
'Do it again
Next
 
End Sub

I am new at this still learning.
Any help would be greatly appreciated.
Thanks
John

 

[Milo_Minderbinder]

Without checking all the code this is what I think your problem is: your rngCurrent should only be one column since you are later using offset.

also

If rngCell.Value < 79 Then

s/b be

If rngCell.offset(0,5).Value < 79 Then

You could also use "else" instead of a second if to deal with the averages above 79.

 

[jlbutcher]

Milo, thank you for your reply.

I am getting an error here in my calculations. Any suggestions?

 rngCell.Offset(0, 5).Value = _
      (Val(rngCell.Offset(0, 0).Value + Val(rngCell.Offset(0, 1).Value + _
            Val(rngCell.Offset(0, 2).Value + Val(rngCell.Offset(0, 3).Value)) / 4)))

 

[jlbutcher]

I hate to do this.....bump. I really need some help on this.
Thanks you

 

[Milo_Minderbinder]

For this

Book1
	A	B	C	D	E	F	G	H	I	J
1	Employee#	FirstName	LastName	Period1	Period2	Period3	Period4	LastReview	Average	Results
2	1	Griffin	Ellett	88	100	22	23	3/25/20090:00	58.25	Reschedule
3	3	Cole	Ellett	64	66	100	99	3/25/20090:00	82.25	DoingFine
Sheet1

try

'Set the range
Set rngCurrent = Range("I2:I" & Range("a65536").End(xlUp).Row).Cells
'Start looping through the range using a variable rngCell
 
For Each rngcell In rngCurrent
 
 
       
      rngcell.Value = WorksheetFunction.Average(Range("D" & rngcell.Row & ":G" & rngcell.Row))
      
      
      If rngcell.Value< 79 Then
      rngcell.Offset(0, 1).Value = "Reschedule"
 Else
    rngcell.Offset(0, 1).Value = "Doing Fine"
 
     End If

Next

 

[jlbutcher]

Milo, thank you very much for your help. It works great.

John