excel - 范围类型不匹配 vba

Question

在将范围值与 "" 进行比较时出现类型不匹配错误或 vbNullString .我阅读了许多处理此问题的类似 q+a 帖子。

数据全部为数字或"" .

Sub vegetableCounting()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim ws1Range As Excel.range, ws2Range As Excel.range, ws3Range As Excel.range, ws2Loop As Excel.range
Dim ws1Row As Long, ws1Col As Long, ws2Row As Long, ws2Col As Long
'
Dim rowCounter As Long, colCounter As Long, rowsMendo As Long
Dim mendoSum As Double
'
Set ws1 = Sheets("shareSchedule")
Set ws2 = Sheets("shareDistribution")
Set ws3 = Sheets("vegCount")
'***not yet set to the full ranges***
Set ws1Range = ws1.range("E7:H11") 'shareSchedule
Set ws2Range = ws2.range("D7:BB17") 'shareDistribution
Set ws3Range = ws3.range("D7:BB11") 'vegetableCount
'***not yet set to the full ranges***
rowsMendo = 0
rowCounter = 0
colCounter = 0
mendoSum = 0

For ws1Row = 0 To ws1Range.Rows.count Step 1
    For ws1Col = 0 To ws1Range.Columns.count Step 1
        If ws1Range.Offset(ws1Row, ws1Col).value <> "" Then
            For Each ws2Loop In ws2Range '11rows*51cols = 561
                ws2Row = ws2Row + rowCounter + rowsMendo
                ws2Col = ws2Col + colCounter
                If ws2Range.Offset(ws2Row, ws2Col).value = "" Then
                    Exit For
                Else
                    If ws1Range.Offset(ws1Row, ws1Col).Interior.ColorIndex = 24 And _
                    ws2Range.Offset(ws2Row, ws2Col).Interior.ColorIndex = 24 Then 'a MENDO match
                        If rowCounter < 3 Then
                            mendoSum = mendoSum + ws1Range.Offset(ws1Row, ws1Col).value * ws2Range.Offset(ws2Col, ws2Row)
                            rowCounter = rowCounter + 1
                        ElseIf rowCounter = 3 Then
                            colCounter = colCounter + 1
                            rowCounter = 0
                        ElseIf colCounter = ws2Range.Columns.count + 1 And _
                        ws2Range.Offset(ws2Row, 1).Interior.ColorIndex = 24 And _
                        ws2Range.Offset(ws2Row + 4, 1).Interior.ColorIndex = 24 Then
                            colCounter = 0
                            rowsMendo = rowsMendo + 3
                        ElseIf colCounter = ws2Range.Columns.count + 1 And _
                        ws2Range.Offset(ws2Row, 1).Interior.ColorIndex = xlNone And _
                        ws2Range.Offset(ws2Row + 4, 1).Interior.ColorIndex = xlNone Then
                            colCounter = 0
                            rowsMendo = rowsMendo + 1
                        End If

                        ws3Range.Offset(ws1Row, ws2Col) = ws1Range.Offset(ws1Row, ws1Col).value * ws2Range.Offset(ws2Row, ws2Col).value

                    End If
                End If
            Next
        End If
    Next ws1Col
Next ws1Row

'for ws2
                'Offset(0, 0), Offset(1, 0), Offset(2, 0), then
                'Offset(0, 1), Offset(1, 1), Offset(2, 1), then
                'Offset(0, 2), Offset(1, 2), Offset(2, 2), then
                'etc
End Sub

我得到了错误

  If ws1Range.Offset(ws1Row, ws1Col).value <> "" Then

并且很可能会再次使用它

If ws2Range.Offset(ws2Row, ws2Col).value = "" Then

有什么想法吗？这是我试图从中提取的工作表的一些图像

Answer 1

你可以试试CStr将值转换为字符串。 Format也可用于处理 Null而CStr会产生错误。

所以要么:
If CStr(ws1Range.Offset(ws1Row, ws1Col).value) <> "" Then
或者
If Format(ws1Range.Offset(ws1Row, ws1Col).value) <> "" Then