Results 1 to 5 of 5

Thread: counter i not working inside cell reference

  1. #1

    counter i not working inside cell reference



    Register for a FREE account, and/
    or Log in to avoid these ads!

    • 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    Do 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
    Attached Files Attached Files

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    175
    Articles
    0
    Excel Version
    2010
    You don't appear to be incrementing i anywhere in that code.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  3. #3
    how that? how can I fix it?

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    175
    Articles
    0
    Excel Version
    2010
    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
    Circumference of a circle = 2πrē



    ēthe circle's radius

  5. #5
    thank you it works perfectly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •