VBA:IsEmpty 内的函数单元格

Question

你能帮我解决这个 VBA 代码吗？我认为问题在于 IsEmpty 函数中的 Cells 函数，即 Excel 指责错误的行。由于评论，我做了一些更改，但它仍然没有运行。问题在于 IsEmpty 函数的行。

    Sub preenche_lacunas()

Dim linha_um As Integer
Dim linha_inicio As Integer
Dim linha_final As Integer
Dim coluna_um As Integer
Dim coluna_inicio As Integer
Dim coluna_final As Integer
Dim preenche_com As Integer    


    linha_inicio = 5
    linha_final = 101

    coluna_inicio = 2
    coluna_final = 16

    preenche_com = 0

    linha_um = linha_inicio
    Do While (linha_um <= linha_final)

        coluna_um = coluna_inicio
        Do While (coluna_um <= coluna_final)

            If IsEmpty(Cells(linha_um, coluna_um)) Then

                Cells(linha_um, coluna_um) = preenche_com
            End If

            coluna_um = coluna_um + 1
        Loop

        linha_um = linha_um + 1
    Loop

    End Sub

Answer 1

看起来您正在尝试从 5 到 101 循环遍历 linha(即行)和从 B 到 P(2 到 16)的 coluna(即列)，并在任何空白单元格中放置一个零。

with worksheets("Sheet1")
    with .range(.cells(5, 2), .cells(101, 16))
        'make sure that .UsedRange extends at least as far as the extents
        if isempty(.cells(.rows.count, .columns.count)) then _
            .cells(.rows.count, .columns.count) = 0
        'skip over if there are no blank cells
        on error resume next
        .specialcells(xlcelltypeblanks) = 0
        'reset standard error protocol
        on error goto 0
    end with
end with

请记住，包含零长度字符串的单元格(例如 "" 或 TEXT(,) 来自公式)并不是真正的空白。