Excel VBA编译错误：Type Mismatch，.Row行报错求助

错误核心原因 #

你的代码出现Type Mismatch（类型不匹配）和Object Required（需要对象）错误，核心是两个问题：

1. 变量声明不规范：VBA多变量同一行声明时，只有最后一个变量会被指定类型，其余默认是Variant类型。比如Dim DSDestWH, NSDestWH, DSWorkPool, NSWorkPool As Range实际只有NSWorkPool是Range类型，其他三个都是Variant，后续赋值行号时触发类型错误。
2. 类型赋值错误：.Row返回的是数值类型（Long），但你用Set关键字给Range类型变量赋值（Set DSQTY = RDDS.Range("F2").End(xlUp).Row），Set仅用于对象赋值，数值不能用Set；同时SumIfs需要的是Range对象，不是行号数值，这也会导致后续调用失败。

修复步骤 #

1. 逐个明确变量类型，避免Variant隐式类型问题
2. 正确获取数据区域的最后一行行号，再构建完整的Range对象（而非只存行号）
3. 去掉数值类型变量前的Set关键字，对象类型变量保留Set

完整修正代码 #

Sub DDS_SUMIFS()
    Dim DSQTY As Range
    Dim NSQTY As Range ' 修正为Range类型，适配SumIfs参数要求
    Dim DSDestWH As Range, NSDestWH As Range, DSWorkPool As Range, NSWorkPool As Range
    Dim RDDS As Worksheet, RDNS As Worksheet
    Dim ABQ1 As String, BFI4 As String, CLE2 As String, DEN3 As String, DEN4 As String
    Dim GEG1 As String, LIT1 As String, ORD5 As String, ORF3 As String, PAE2 As String
    Dim PCW1 As String, PDX9 As String, SLC1 As String, SMF1 As String
    Dim lastRowDS As Long, lastRowNS As Long

    Set RDDS = Sheets("Raw Data - DS")
    Set RDNS = Sheets("Raw Data - NS")

    ' 从列底向上找最后一行，避免F2上方有空白导致的范围错误
    lastRowDS = RDDS.Cells(RDDS.Rows.Count, "F").End(xlUp).Row
    lastRowNS = RDNS.Cells(RDNS.Rows.Count, "F").End(xlUp).Row

    ' 构建完整的Range数据范围
    Set DSQTY = RDDS.Range("F2:F" & lastRowDS)
    Set NSQTY = RDNS.Range("F2:F" & lastRowNS)
    Set DSDestWH = RDDS.Range("A2:A" & lastRowDS)
    Set NSDestWH = RDNS.Range("A2:A" & lastRowNS)
    Set DSWorkPool = RDDS.Range("H2:H" & lastRowDS) ' 对应原宏里的C8列（WorkPool字段）
    Set NSWorkPool = RDNS.Range("H2:H" & lastRowNS)

    ABQ1 = "ABQ1"
    BFI4 = "BFI4"
    CLE2 = "CLE2"
    DEN3 = "DEN3"
    DEN4 = "DEN4"
    GEG1 = "GEG1"
    LIT1 = "LIT1"
    ORD5 = "ORD5"
    ORF3 = "ORF3"
    PAE2 = "PAE2"
    PCW1 = "PCW1"
    PDX9 = "PDX9"
    SLC1 = "SLC1"
    SMF1 = "SMF1"

    ' PNYP Column
    Range("H33") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ABQ1, DSWorkPool, "PNYP")
    Range("H34") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, BFI4, DSWorkPool, "PNYP")
    Range("H35") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, CLE2, DSWorkPool, "PNYP")
    Range("H36") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN3, DSWorkPool, "PNYP")
    Range("H37") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN4, DSWorkPool, "PNYP")
    Range("H38") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, GEG1, DSWorkPool, "PNYP")
    Range("H39") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, LIT1, DSWorkPool, "PNYP")
    Range("H40") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORD5, DSWorkPool, "PNYP")
    Range("H41") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORF3, DSWorkPool, "PNYP")
    Range("H42") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, PAE2, DSWorkPool, "PNYP")
    Range("H43") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PCW1, NSWorkPool, "PNYP")
    Range("H44") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PDX9, NSWorkPool, "PNYP")
    Range("H45") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SLC1, NSWorkPool, "PNYP")
    Range("H46") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SMF1, NSWorkPool, "PNYP")

    ' PP Column（改为I列，避免覆盖PNYP数据）
    Range("I33") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ABQ1, DSWorkPool, "PP")
    Range("I34") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, BFI4, DSWorkPool, "PP")
    Range("I35") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, CLE2, DSWorkPool, "PP")
    Range("I36") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN3, DSWorkPool, "PP")
    Range("I37") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN4, DSWorkPool, "PP")
    Range("I38") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, GEG1, DSWorkPool, "PP")
    Range("I39") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, LIT1, DSWorkPool, "PP")
    Range("I40") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORD5, DSWorkPool, "PP")
    Range("I41") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORF3, DSWorkPool, "PP")
    Range("I42") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, PAE2, DSWorkPool, "PP")
    Range("I43") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PCW1, NSWorkPool, "PP")
    Range("I44") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PDX9, NSWorkPool, "PP")
    Range("I45") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SLC1, NSWorkPool, "PP")
    Range("I46") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SMF1, NSWorkPool, "PP")

    ' LD Column（改为J列，避免覆盖PP数据）
    Range("J33") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ABQ1, DSWorkPool, "LD")
    Range("J34") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, BFI4, DSWorkPool, "LD")
    Range("J35") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, CLE2, DSWorkPool, "LD")
    Range("J36") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN3, DSWorkPool, "LD")
    Range("J37") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, DEN4, DSWorkPool, "LD")
    Range("J38") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, GEG1, DSWorkPool, "LD")
    Range("J39") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, LIT1, DSWorkPool, "LD")
    Range("J40") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORD5, DSWorkPool, "LD")
    Range("J41") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, ORF3, DSWorkPool, "LD")
    Range("J42") = WorksheetFunction.SumIfs(DSQTY, DSDestWH, PAE2, DSWorkPool, "LD")
    Range("J43") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PCW1, NSWorkPool, "LD")
    Range("J44") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, PDX9, NSWorkPool, "LD")
    Range("J45") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SLC1, NSWorkPool, "LD")
    Range("J46") = WorksheetFunction.SumIfs(NSQTY, NSDestWH, SMF1, NSWorkPool, "LD")

End Sub

额外优化建议 #

• 避免数据覆盖：原代码中三个分类（PNYP/PP/LD）都写入H列，会导致数据被覆盖，已修正为H/I/J三列，可根据实际需求调整
• 循环简化代码：重复的SumIfs语句可用数组循环减少冗余，示例：

Dim whList As Variant, rowOffset As Integer
whList = Array("ABQ1", "BFI4", "CLE2", "DEN3", "DEN4", "GEG1", "LIT1", "ORD5", "ORF3", "PAE2", "PCW1", "PDX9", "SLC1", "SMF1")
For rowOffset = 0 To UBound(whList)
    Sheets("Dock Door Status").Range("H33").Offset(rowOffset, 0) = WorksheetFunction.SumIfs(DSQTY, DSDestWH, whList(rowOffset), DSWorkPool, "PNYP")
    Sheets("Dock Door Status").Range("I33").Offset(rowOffset, 0) = WorksheetFunction.SumIfs(DSQTY, DSDestWH, whList(rowOffset), DSWorkPool, "PP")
    Sheets("Dock Door Status").Range("J33").Offset(rowOffset, 0) = WorksheetFunction.SumIfs(DSQTY, DSDestWH, whList(rowOffset), DSWorkPool, "LD")
Next

• 指定目标工作表：对Range("H33")这类操作，建议加上工作表限定（比如Sheets("Dock Door Status").Range("H33")），避免当前工作表切换导致错误

内容的提问来源于stack exchange，提问作者Iron Man