Debugging Loop script

  • Thread starter Thread starter Ciara
  • Start date Start date
C

Ciara

Hi,
I am trying to debug a script as it gives the incorrect results. I
think it has something to do with the count_attendance function .

Function Count_Attendance(ATT_TYPE_CODE, SPEC_CDE_NO, ATT_TYPE)

'If ATT_TYPE is passed in as "Did Not Attend" then counts DNAs
'If ATT_TYPE_CODE = 1 then counts First Attendances
'If ATT_TYPE_CODE = 2 then counts Subsequent Attendances

Sheets("QM0P_DETAIL").Select
Row = 2 'First row in QM08
Detail
ATT_TYPE_COL = 32
DNA_COL = 29
x = 0
Col = 9 'Spec Code in QM0P

Dim COUNT As Integer
COUNT = 0

Do Until Cells((Row + x), Col) = ""
If Cells((Row + x), DNA_COL) = ATT_TYPE Then
'Check DNA Status
If Cells((Row + x), Col) = SPEC_CDE_NO And Cells((Row +
x), ATT_TYPE_COL) = ATT_TYPE_CODE Then
COUNT = COUNT + 1
End If
Else 'The Else will
only come into play if Not counting DNAs
If Cells((Row + x), Col) = SPEC_CDE_NO And Cells((Row +
x), ATT_TYPE_COL) = ATT_TYPE_CODE Then
COUNT = COUNT + 1
End If
End If

x = x + 1
Loop

Count_Attendance = COUNT

End Function

Below is the subroutine that pastes the count into the column.
' ***** START QM0P Subsequent Attendance, DNA Count FOR QM08s *****

Sheets("Korner_QM08s").Select 'Select First
Specialty Cell in <1 Week Column
Row = 11
Col = 27 'Set the column to the
Subs Att, DNA Column
Cells(Row, Col).Select

Specialty_Count = Row - 11
Do Until Specialty_Count = (SPEC_CDE_TOTAL + 1) 'Inner Loop
for looping through specialties

Test = Count_Attendance(2, SPEC_CDE_NO(Specialty_Count),
"Did Not Attend") 'Call Function to Count Referrals for GP Refs
Sheets("Korner_QM08s").Select
Cells(Row, Col).Select
Selection.Value = Test 'Populate the Korner Sheet
with the count

Specialty_Count = Specialty_Count + 1
Row = Row + 1
Loop 'End loop for
Specialty looping
Col = Col + 1

COUNT = 0

End Sub

The result of the above should return 2 records, instead it returns 16
records. I know this might be a bit much, but any help would be
greatly appreaciated.
 
Clara: do you know how do use the debug feattures in VBA? the only real way
of find the problem is put in break points and stepping through code. Iff
you find the place in the code where the problem occurs, then somebody can
help

Use F9 in VBA window to add break points
variables can be seen either by moving the mouse over the variable name or
add the variable tto the watch window.

You may need to add extra lines of code tto see intermediate results

add line above if if you need to watch intermediate calculations
abc = Cells((Row + x), col)

If Cells((Row + x), Col) = SPEC_CDE_NO And Cells((Row +
x), ATT_TYPE_COL) = ATT_TYPE_CODE Then
 
Is SPEC_CDE_NO a Function? If it is what does it do? Looks like you are
testing for the value "Did Not Attend" and a numerical value SPEC_CDE_NO in
order to increment COUNT. Is that correct?
 
Back
Top