Home / Questions / 326884
Accepted
JCstdio
Asked: 2020-02-06 01:30:49 +0800 CST 2020-02-06 01:30:49 +0800 CST

VBA Skipping an error in a With...End With

  • 772

I am working with vba for data processing within excel, I have some reports, each one on its own sheet, in which I need to fill in the cells that are blank with the value of the cell above them.

The problem is that when I finish the first sheet, it gives me a 1004 error, and I would like it to go past that error, and continue with the next sheet. The code I have is the following:

Function structure_sheets()
    With ThisWorkbook
        For Each Sheet In .Sheets
            If (InStrRev(Sheet.Name, "Node") > 0) Then
                LastRow = Sheet.UsedRange.Rows.Count
                With Range("A8:F" & Range("F" & LastRow).End(xlDown).Row)
                    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
                    .Value = .Value
                End With
            End If
        Next
    End With
End Function

And the error:

enter image description here

OnErrors don't seem to work with these blocks of code.

If anyone has an idea, suggestions are welcome.

Thank you very much.

vba

1 Answers

  1. Best Answer

    Let's go for points:

    • Functions ( Function structure_sheets()) are used to return a value, in case you don't return anything, which is this case, you should use the Sub.
    • It is recommended, if not required, to declare all the variables you use. To help you with this, put Option Explicitat the top of your module. It will force you to declare all variables.
    • You don't need to use the With ThisWorkbooksince you only reference it once.
    • When you calculate LastRowyou correctly reference the sheet (in turn, referenced to the workbook because it is inside the loop that runs through it), but when you do the Wtih Range...you never make reference to the sheet, so if the active sheet <> the worksheet loop, that's not going to work for you. You should always make full references to ranges: book-sheet-range.
    • Lastly, you can avoid the error simply by counting ahead if you are going to run into empty cells.

    Taking all of the above into account, this is what your code would look like:

    Option Explicit
Sub structure_sheets()

    Dim Sheet As Worksheet
    Dim LastRow As Long
    Dim MiRango As Range
    Dim ContadorVacias As Long
    For Each Sheet In ThisWorkbook.Sheets
        If (InStrRev(Sheet.Name, "Node") > 0) Then
            LastRow = Sheet.UsedRange.Rows.Count
            Set MiRango = Sheet.Range("A8:F" & Sheet.Range("F" & LastRow).End(xlDown).Row)
            ContadorVacias = MiRango.SpecialCells(xlCellTypeBlanks).Cells.Count
            If ContadorVacias = 0 Then GoTo Siguiente
            With MiRango
                .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
                .Value = .Value
            End With
        End If
Siguiente:
        ContadorVacias = 0
    Next

End Sub
    • 4