title: why does my VBA code that works in module not work as expected when assigned to a worksheet and a button
body: <p>I have a workbook that is essentially an automated test, marking and feedback tool for end of topic tests for students. On the '701Test' sheetThey input their teaching group via a drop down list and the select their from subsequent list. They answer the multiple choice questions and press a button when finished. The button takes them to a 'results' page which gives their marks for each question, give feedback for incorrect answers and gives a total score. They then hit the finish button which generates a PDF copy of the mark sheet in their my documents folder and then emails a copy to themselves and the Schools email account. At this point I also wanted to post the final score to the students record on a central registry using a loop through the student list to find the name and offset to post the Score value from the 'Results' page and finally return to the test page. This last bit I wrote the code for in a module and it executes perfectly, but when added to the main code and run from the button the loop part fails to execute but the return to the test page does work, but no error is recorded for the loop failure.</p>
  
  <p>Here is the 'Results' page code in full the 'With Central reg' bit at the bottom is the problem, any help is greatly appreciated.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
    Dim IsCreated As Boolean
    Dim PdfFile As String, Title As String
    Dim OutlApp As Object
    Dim cell As Range
    Dim Students As Range
  
    Title = Range("D1").Value
    sname = Range("B2").Value
    PdfFile = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") &amp; "\" &amp; sname &amp; Title &amp; ".pdf"
  
  
    With ActiveSheet
      .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
  
    On Error Resume Next
    Set OutlApp = GetObject(, "Outlook.Application")
    If Err Then
      Set OutlApp = CreateObject("Outlook.Application")
      IsCreated = True
    End If
    OutlApp.Visible = True
    On Error GoTo 0
  
    With OutlApp.CreateItem(0)
  
      .Subject = Title
      .to = Range("B2").Value  ' &lt;-- Put email of the recipient here"
      .CC = "" ' &lt;-- Put email of 'copy to' recipient here
      .Body = "Hi," &amp; vbLf &amp; vbLf _
            &amp; "Yr 7 701 EOT test attached in PDF format." &amp; vbLf &amp; vbLf _
            &amp; "Regards," &amp; vbLf _
            &amp; "KDS ICT Dept" &amp; vbLf &amp; vbLf
      .Attachments.Add PdfFile
  
      Application.Visible = True
      .Display
    End With
  
    If IsCreated Then OutlApp.Quit
  
    Set OutlApp = Nothing
  
  
    With CentralReg
  
  
      For Each cell In Range("A2:A250")
      If cell = Range("Results!B2").Value Then
      cell.Offset(0, 4).Activate
      ActiveCell.Value = Range("Results!B27").Value
      End If
      Next
  
  
  End With
  
  End Sub 
  </code></pre>