创建一个excel宏来检查列值并将它们加在一起 [英] Creating an excel macro to check the column values and sum them together

问题描述

我是新的excel宏，我需要创建一个excel宏，它将检查单元格中的数字的前4个字符，如果它匹配不同列中的单元格中的其他4个字符，那么在单独的列需要总结在一起，并且位于两个数字匹配的第一个实例位于不同的名为LE Internal的列中。

下面的链接示例

I am new to excel macros and I need to create an excel macro which will check the first 4 characters of a number in a cell and if it matches the other 4 characters in a cell in a different columns then the values in a separate column need to be summed together and places at the first instance where the two numbers matches in a different column named LE Internal.

Example on link below
Example 1 Example 2

解决方案

Try this:

Sub Demo()
    Dim dict1 As Object
    Dim c1 As Variant, k As Variant
    Dim currWS As Worksheet
    Dim i As Long, lastRow As Long, tot As Long
    Dim number1 As Long, number2 As Long, firstRow As Long

    Set dict1 = CreateObject("Scripting.Dictionary")
    Set currWS = ThisWorkbook.Sheets("Sheet2") '-->change Sheet1 to your work sheet name

    'get last row withh data in Column A
    lastRow = currWS.Cells(Rows.count, "A").End(xlUp).Row

    'put unique numbers in Column A in dict1
    c1 = Range("A2:B" & lastRow)
    For i = 1 To UBound(c1, 1)
        If c1(i, 1) <> "" Then
            'make combination with first 4 characters
            dict1(Left(c1(i, 1), 4) & "," & Left(c1(i, 2), 4)) = 1
        End If
    Next i

    'loop through all the numbers in column A
    For Each k In dict1.keys
        number1 = Split(k, ",")(0)
        number2 = Split(k, ",")(1)
        tot = 0
        firstRow = 0

        For i = 2 To lastRow
            If k = Left(currWS.Range("A" & i).Value, 4) & "," & Left(currWS.Range("B" & i).Value, 4) Then
                If firstRow = 0 Then
                    firstRow = i
                End If
                tot = tot + currWS.Range("C" & i).Value
            End If
        Next i
        currWS.Range("D" & firstRow) = tot
    Next k
End Sub

See image for reference:

这篇关于创建一个excel宏来检查列值并将它们加在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！