VBA代码rng.Find行出现类型不兼容错误排查求助

我编写了一段VBA代码，目标是调整列数据，使每行的求和结果为10。但在rng.Find语句处遇到了“类型不兼容”错误，需要分析该错误的成因。

代码逻辑说明 #

每列仅包含最小值和最大值两种数值，先计算每行的求和结果，若结果不等于10，则交换当前行内当前列的最值与同列其他单元格的数值，重新计算求和并重复该过程，直到求和结果为10，再处理下一列，以此类推完成所有行的处理。

执行前后截图 #

原代码 #

Sub OrganizarParaSoma10()
Dim ws As Worksheet
    Dim rng As Range
    Dim row As Range
    Dim coluna As Integer
    Dim valorRestante As Integer
    Dim MinValor As Double
    Dim MaxValor As Double
    Dim TempValor As Double
    Dim TempCelula As Range
    Dim celula As Range
    Dim i As Integer
    Dim j As Integer
    
    ' Defina a planilha onde estão os dados
    Set ws = ThisWorkbook.Sheets("Planilha2")
    qtdeLinhas = Range("B5").End(xlDown).row
    ' Defina a faixa de células que deseja rearranjar (substitua "A1:J10" pela faixa correta)
    Set rng = ws.Range("Arrumar")
    
    ' Itera sobre cada linha na faixa
    For j = 5 To qtdeLinhas
        Dim soma As Integer
        soma = 0
        For i = 2 To 6
        soma = soma + Cells(j, i) + Cells(j, i + 8)
        Next i
        
        If soma <> 10 Then
            For Each col In rng.Columns
            MinValor = WorksheetFunction.Min(col)
            MaxValor = WorksheetFunction.Max(col)
            
            If col.Cells(j).Value = MinValor Then
                
                Set TempCelula = rng.Find(What:=MaxValor, After:=rng.Cells(j, col), LookIn:=xlValues, LookAt:=xlWhole)
                
                TempValor = TempCelula.Value
                celula = col.Cells(j).Value
                col.Cells(j).Value = TempValor

                soma = 0
                For i = 2 To 6
                soma = soma + Cells(j, i) + Cells(j, i + 8)
                Next i
                
                If soma = 10 Then
                    Exit For
                End If
            
            ElseIf col.Cells(j).Value = MaxValor Then
                
                Set TempCelula = rng.Find(What:=MinValor, After:=rng.Cells(j, col), LookIn:=xlValues, LookAt:=xlWhole)
                
                TempValor = TempCelula.Value
                celula = col.Cells(j).Value
                col.Cells(j).Value = TempValor

                soma = 0
                For i = 2 To 6
                soma = soma + Cells(j, i) + Cells(j, i + 8)
                Next i
                
                If soma = 10 Then
                    Exit For
                End If
            End If
            
            Next col
        
        End If
    Next j
    
        
       
End Sub

错误成因分析 #

1. rng.Find的After参数类型错误
   循环中For Each col In rng.Columns里的col是Range对象，而rng.Cells(j, col)的第二个参数需要的是列索引（整数或列标字符串），不是Range对象，这直接触发“类型不兼容”错误。正确写法应使用col.Cells(j)作为After的起始单元格，因为该参数要求传入单个单元格对象。

2. Range对象赋值错误
   代码中celula = col.Cells(j).Value，但celula被声明为Range对象，赋值Range对象必须使用Set关键字，否则会引发类型不兼容错误，正确写法为Set celula = col.Cells(j)。

3. 未指定工作表的Cells引用
   代码中Cells(j, i)未限定所属工作表，默认指向当前活动工作表，若活动表不是目标表ws（Planilha2），会导致数据读取错误，应改为ws.Cells(j, i)。

4. 未限定工作表的Range引用
   qtdeLinhas = Range("B5").End(xlDown).row同样未指定工作表，应改为qtdeLinhas = ws.Range("B5").End(xlDown).Row，避免引用错误工作表的数据。

修正后的关键代码片段 #

' 修正After参数，传入正确的单元格对象
Set TempCelula = rng.Find(What:=MaxValor, After:=col.Cells(j), LookIn:=xlValues, LookAt:=xlWhole)

' 修正Range对象赋值，补充完整交换逻辑
Set celula = col.Cells(j)
TempValor = TempCelula.Value
col.Cells(j).Value = TempCelula.Value
TempCelula.Value = celula.Value

' 限定Cells到指定工作表，避免引用错误
soma = soma + ws.Cells(j, i) + ws.Cells(j, i + 8)

内容的提问来源于stack exchange，提问作者Lucas Olivier