Why is a line skipped when running my code?

Question

I'm having issues while running this code. Whenever I do it step by step while pressing F8 it works but whenever I run it skips the Rows(R).EntireRow.Insert line which is the most important. Thank you!

Sub AddARow()
    Dim R As Long
    Dim FoundCell As Range
    Dim revF As Long
    Dim nbUnit As Long
    Dim moyenneM As Long
    
    Set FoundCell = Sheets("Étude").Range("C1:C200").Find(what:="xxxxx")
    R = ((FoundCell.Row) + 2)
    
    Rows(R).EntireRow.Insert
    
    Cells(R, 3).Value = "Moyenne mensuelle par condo"
    
    nbUnit = Cells((R + 4), 4).Value
    
    For i = 4 To 33
        revF = Cells((R - 1), i).Value
        moyenneM = revF / nbUnit
    
        Cells(R, i).Value = moyenneM / 12
    Next
    
    Call AutoFill_TB
End Sub

are you sure it's not just inserting the line on a different sheet? By default (since you don't specify a sheet) it will use the active sheet. — Tim Williams, Mar 07 '21 at 04:54

score 0 · Accepted Answer · answered Mar 07 '21 at 07:03

Its a good practice to explicitly provide the sheet name on which you are performing the action. You can do this by declaring a variable for sheet and a workbook and set that variable. I have modified your code to provide these variables. If you follow this, you will not face the issue, you are currently getting:

Sub AddARow()
    Dim R As Long
    Dim FoundCell As Range
    Dim revF As Long
    Dim nbUnit As Long
    Dim moyenneM As Long
    Dim sh As Worksheet
    Dim wkb As Workbook
    
    Set wkb = ThisWorkbook
    Set sh = wkb.Worksheets("Étude")
    
    Set FoundCell = sh("Étude").Range("C1:C200").Find(what:="xxxxx")
                R = ((FoundCell.Row) + 2)
                
    
    sh.Rows(R).EntireRow.Insert    ' assuming you want to insert in Sheet - Étude
    
    
    sh.Cells(R, 3).Value = "Moyenne mensuelle par condo"
    
    nbUnit = sh.Cells((R + 4), 4).Value
    
    
    For i = 4 To 33
    
        revF = sh.Cells((R - 1), i).Value
        moyenneM = revF / nbUnit
    
        sh.Cells(R, i).Value = moyenneM / 12
    
    Next
    
    Call AutoFill_TB
End Sub

Why is a line skipped when running my code?

1 Answers1