counter i not working inside cell reference

chiidzzz

New member
Joined
Aug 28, 2012
Messages
11
Reaction score
0
Points
0
  • Hi, I am trying to write a macro that automatically activates the sheet that has the same name in Cell A14 to do some changes.
    It should loop from A14 through A36 and do the same thing each time.
    this is the code I have written, but it does not work.
    Code:
    Sub Button5_Click()    Dim myDestSheet As Worksheet    Dim destRow As Long    Dim i As Integer    total As Integer    With ThisWorkbook    [FONT=Verdana]Do [/FONT]While i < 23total = 13 + i        If sheet16.Cells(" & total & ", 2) = "P" Then            On Error Resume Next            Set myDestSheet = Worksheets(Cells(" & total & ", 1).Value)                    destRow = myDestSheet.Cells(Rows.Count, "a").End(xlUp).Row + 1                    Application.EnableEvents = False                    With myDestSheet                        .Cells(destRow, 1) = Date                        .Cells(destRow, 2) = sheet16.Cells(" & total & ", 2).Value                        .Cells(destRow, 3) = Cells(6, 2).Value                        .Cells(destRow, 4) = Cells(7, 2).Value                        .Cells(destRow, 5) = Cells(8, 2).Value                        .Cells(destRow, 6) = Cells(9, 2).Value                        .Cells(destRow, 7) = Cells(10, 2).Value                    End With                    Application.EnableEvents = True                                    End Ifloop    End WithEnd Sub
 

Attachments

  • Attendance_3.xlsm
    24.5 KB · Views: 12
You don't appear to be incrementing i anywhere in that code.
 
Since you have a fixed loop size, you should use a For...Next loop:
Code:
Sub Button5_Click()
   Dim myDestSheet                 As Worksheet
   Dim destRow                     As Long
   Dim i                           As Integer
   Dim total                       As Integer
   With ThisWorkbook

      For i = 1 To 23

         total = 13 + i

         If Sheet16.Cells(total, 2) = "P" Then
            On Error Resume Next
            Set myDestSheet = Worksheets("" & Cells(total, 1).Value)
            destRow = myDestSheet.Cells(Rows.Count, "a").End(xlUp).Row + 1
            Application.EnableEvents = False
            With myDestSheet
               .Cells(destRow, 1) = Date
               .Cells(destRow, 2) = Sheet16.Cells(" & total & ", 2).Value
               .Cells(destRow, 3) = Cells(6, 2).Value
               .Cells(destRow, 4) = Cells(7, 2).Value
               .Cells(destRow, 5) = Cells(8, 2).Value
               .Cells(destRow, 6) = Cells(9, 2).Value
               .Cells(destRow, 7) = Cells(10, 2).Value
            End With
            Application.EnableEvents = True

         End If
      Next i
   End With
End Sub
 
Back
Top