基本上，我的想法是我需要根据Sheet1中的特定列对数字的总和进行分组，并在Sheet2中进行更新...现在下面的代码在Key one上抛出类型不匹配的错误。列（9，10，11，18，19，20，21）是表1中的关键列，我需要根据表2中的唯一列对表1中的总和进行分组并进行更新

Sub UpdatePremiumValues()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    Dim dataRange1 As Range
    Dim dataRange2 As Range
    Dim cell As Range
    Dim dict As Object
    
    ' Set references to the worksheets
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' Find the last row with data in Sheet1 and Sheet2
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' Set the data ranges in Sheet1 and Sheet2
    Set dataRange1 = ws1.Range("A2:AI" & lastRow1) ' Adjust columns as needed
    Set dataRange2 = ws2.Range("A2:AI" & lastRow2) ' Adjust columns as needed
    
    ' Create a dictionary to store grouping and sum data
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Loop through data in Sheet1 and update dictionary
    For Each cell In dataRange1.Rows
        Dim key As String
        Dim values(1 To 6) As Double
           
    ' Construct the key string
        key = Join(Application.Index(cell.Resize(1, 7).Value, 1, Array(9, 10, 11, 18, 19, 20, 21)), "|")
        ' Check if the value in each column is numeric
        For i = 1 To 7
            If Not IsNumeric(cell.Cells(1, i).Value) Then
                Exit For
            End If
        Next i
        
        ' Add the key to the dictionary
        dict(key) = values
     
        
        Debug.Print "Key: " & key
        If dict.Exists(key) Then
            ' Update values based on grouping
            dict(key)(1) = dict(key)(1) + cell.Cells(1, 28).Value ' Net Premium (SGD)
            dict(key)(2) = dict(key)(2) + cell.Cells(1, 29).Value ' 1st Premium (SGD)
            dict(key)(3) = dict(key)(3) + cell.Cells(1, 30).Value ' 2nd Premium (SGD)
            dict(key)(4) = dict(key)(4) + cell.Cells(1, 34).Value ' FO Premium (SGD)
            dict(key)(5) = dict(key)(5) + cell.Cells(1, 35).Value ' Fac HO Premium (SGD)
            dict(key)(6) = dict(key)(6) + cell.Cells(1, 36).Value ' Fac Others Premium (SGD)
        Else
            'Dim values(1 To 6) As Double
            values(1) = cell.Cells(1, 28).Value ' Net Premium (SGD)
            values(2) = cell.Cells(1, 29).Value ' 1st Premium (SGD)
            values(3) = cell.Cells(1, 30).Value ' 2nd Premium (SGD)
            values(4) = cell.Cells(1, 34).Value ' FO Premium (SGD)
            values(5) = cell.Cells(1, 35).Value ' Fac HO Premium (SGD)
            values(6) = cell.Cells(1, 36).Value ' Fac Others Premium (SGD)
            dict(key) = values
        End If
    Next cell
    
    ' Update values in Sheet2 based on dictionary
    For Each cell In dataRange2.Rows
          If dict.Exists(key) Then
            ' Update values in Sheet2 based on grouping
            cell.Cells(1, 21).Value = dict(key)(1) ' Net Premium (SGD)
            cell.Cells(1, 22).Value = dict(key)(2) ' 1st Premium (SGD)
            cell.Cells(1, 23).Value = dict(key)(3) ' 2nd Premium (SGD)
            cell.Cells(1, 27).Value = dict(key)(4) ' FO Premium (SGD)
            cell.Cells(1, 28).Value = dict(key)(5) ' Fac HO Premium (SGD)
            cell.Cells(1, 29).Value = dict(key)(6) ' Fac Others Premium (SGD)
        End If
    Next cell
    
    ' Clean up
    Set dict = Nothing
    MsgBox "Values updated based on grouping!"
End Sub

字符串