检查工作表中是否有任何公式 [英] Check if there are any formulas in worksheet

问题描述

我在工作表中运行了一些代码，并突出显示了其中包含公式的所有单元格.代码的这一部分工作正常，但是，如果工作表中没有带有公式的单元格，则代码将崩溃.我想做的是，在电子表格中没有公式的情况下，在其中放入一条if语句将结束代码.我试图遍历单元格并检查是否每个单元格都有一个公式，但是也会崩溃.因此，我要修复的是if语句.

I have a bit of code that runs through my worksheets and highlights all cells with formulas in them. This part of the code works fine, however, if there are no cells with formulas in a worksheet, then the code crashes. What I want to do, is put an if statement in that will end the code if there are no formulas in the spreadsheet. I have tried to loop through the cells and check if there each cell has a formula but that crashes as well. So what I am looking to do is fix the if statement.

任何帮助将不胜感激.

突出显示代码

'Apply yellow highlight to all formula cells.

Dim ws As Worksheet
Dim rng As Range

Set ws = ActiveSheet
For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
rng.Interior.ColorIndex = 36
Next rng

带有if语句的代码

'Apply yellow highlight to all formula cells.

Dim ws As Worksheet
Dim rng As Range

Set ws = ActiveSheet

c = 0
For Each cell in ws.Cells
If cell.HasFormula = True Then
c= c + 1
End If
Next cell

If c > 0 Then
For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
rng.Interior.ColorIndex = 36
Next rng
Else MsgBox ("No formulas in this worksheet")
End If

推荐答案

您可以在代码中使用错误处理.

You may use error handling in the code.

错误恢复时继续将继续在下一行执行，即使发生错误也不会中断脚本.
On Error Goto 0 会在当前过程中禁用已启用的错误处理程序，并将其重置为Nothing.

On Error Resume Next will continue execution on the next line without interrupting the script even when error occurs.
On Error Goto 0 disables enabled error handler in the current procedure and resets it to Nothing.

Sub test()

    Dim ws As Worksheet
    Dim rng As Range, cell As Range

    Set ws = ActiveSheet
    On Error Resume Next
    Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "No cells found"
        Exit Sub
    End If

    For Each cell In rng
        cell.Interior.ColorIndex = 36
    Next

End Sub

这篇关于检查工作表中是否有任何公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！