vba - VBA 是否可以检测该列是否没有值并停止将其百分比制表？

Question

在 Sid 的帮助下，我现在可以将表 1 中的值与表 2 匹配后将 % 制成表格。但现在，我面临这个问题，

以下是我目前面临的问题的图像。

在 Image1 中，只是将一些测试结果列成 %。


经过很多帮助，我现在可以将其制表，但如果您在 H 列之后注意到，结果将继续制表，因为我已将设置一直设置到 Z 列。


我需要为更多列的测试做准备，这就是为什么我将设置设置为 Z 列。我很好奇的是，我是否可以清除所有#DIV/0!当没有更多结果时？

再一次感谢你!

跟进:
我的代码如下:

Sub Macro3()

Dim ws As Worksheet
Dim wsData As String
Dim SearchText As String, Excludetext As String
Dim LastRow As Long, i As Long, j As Long
Dim MyArray() As String
Dim boolContinue As Boolean

'start making Yield_summary into %
'~~> Add/Remove the text here which you want to ignore
Excludetext = "Split,Grade,Wafer,temp,Qty. In,Qty. Out,Bin1,Bin2,Bin3,Bin4,Bin5,Bin6"

'~~> Change this to the relevant sheetname which has the data
wsData = "Sheet1"

MyArray = Split(Excludetext, ",")

Set ws = Sheets("Sheet2")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'Set Column B into %
For i = 1 To LastRow
    boolContinue = True

    For j = 0 To UBound(MyArray)
        SearchText = MyArray(j)
        If ws.Range("A" & i).Value = SearchText Then
            boolContinue = False
            Exit For
        End If
    Next j

    If boolContinue = True Then
        With ws.Range("B" & i)
            .Formula = _
            "=OFFSET(INDIRECT(ADDRESS(INDEX(MATCH(A" & i & _
            "," & wsData & "!$A$1:$A$50,0),1,0),1,1,TRUE,""" & _
            wsData & """)),0,1)/" & wsData & "!B5"
            .NumberFormat = "0.00%"
        End With
    End If
Next i

'Set Column C into %
For i = 1 To LastRow
    boolContinue = True

    For j = 0 To UBound(MyArray)
        SearchText = MyArray(j)
        If ws.Range("A" & i).Value = SearchText Then
            boolContinue = False
            Exit For
        End If
    Next j

    If boolContinue = True Then
        With ws.Range("C" & i)
            .Formula = _
            "=OFFSET(INDIRECT(ADDRESS(INDEX(MATCH(A" & i & _
            "," & wsData & "!$A$1:$A$50,0),1,0),1,1,TRUE,""" & _
            wsData & """)),0,2)/" & wsData & "!C5"
            .NumberFormat = "0.00%"
        End With
    End If
Next i
End sub'

Answer 1

我可以从 Sid's answer 看到对于您之前的问题，您要在单元格中放置一个公式，如下所示:

    With Range("B" & i)
        .Formula = _
        "=OFFSET(INDIRECT(ADDRESS(INDEX(MATCH(A" & i & _
        ",$A$1:$A$45,0),1,0),1,1,1,'Duplicated_Sheet1')),0,1)/$B$5"
        .NumberFormat = "0.00%"
    End With

您可以通过检查公式是否返回错误(例如 #DIV/0! )来跟进，如果是，请删除该公式，因为它没有用:

    If IsError(Range("B" & i)) Then
        Range("B" & i).ClearContents
    End If