for each loop won't loop

[Adam Jasiewicz]

Hi,

I have recently found the true power of using VBA in excell but i am having problems with a "For Each" loop.
I am trying to create a classroom register (i am a teacher) which would be initiated with a button assigned to a macro.
i would like an option box to pop up cycling the name of each student and asking if they are present.
When i run the attached macro it only ever presents me with the first name and does not iterate through the rest of them. I have tried this in a variety of ways but i can not get it to iterate.

Sub classregister()


Dim studentname As String   'defines variables
Dim student As Range        '
Dim present As String       '
Dim absent As String        '
Dim late As String          '
Dim studentno As Integer    '

present = "/"               '
absent = "A"                '
late = "L"                  '

    studenno = 2
    Range("a2").Select ' selects cell to begin iteration
    For Each student In ActiveSheet.Range("A2:A13")
 
 
    studentname = ActiveCell.Offset(, 1).Value 'defines the variable as the name of the student

        Select Case MsgBox(" is " & studentname & " present?", vbYesNoCancel)
            Case vbYes
                ActiveCell.Offset(, 3) = present
            Case vbNo
                ActiveCell.Offset(, 3) = absent
            Case vbCancel
                Exit Sub
        End Select
     
    studentno = studentno + 1
    Next student
 
End Sub

Yes i know some of the definitions are not necessary but i tried everything to make this work.

Thanks for the help in advance.

EDIT: i should mention that column A contains a number that identifies a student (from 2 to 13), B contains first name, C surname. The message box pops up and if i press a response 12 times it closes the macro. Therefore it is looping but it wont move the activecell and therefore the student (i.e. i can mark one student present/absent 12 times).

 

[Adam Jasiewicz]

ok i have managed it by inserting

ActiveCell.Offset(1, 0).Select

into the for loop.

is there a better way of doing this?

 

[NARAYANK991]

Hi Adam ,

Try this :

Sub classregister()
    Dim studentname As String, present As String, absent As String, late As String  'defines variables
    Dim student As Range        '
    Dim studentno As Integer    '

    present = "/"              '
    absent = "A"                '
    late = "L"                  '

    studentno = 1
    Range("a2").Select ' selects cell to begin iteration
    For Each student In ActiveSheet.Range("A2:A13")
        studentname = student.Offset(, 1).Value 'defines the variable as the name of the student

        Select Case MsgBox(" is " & studentname & " present?", vbYesNoCancel)
              Case vbYes
                    student.Offset(, 3) = present
              Case vbNo
                    student.Offset(, 3) = absent
              Case vbCancel
                    Exit Sub
        End Select

        studentno = studentno + 1
    Next student
End Sub

Narayan