今天有一同学,喊我帮忙处理EXCEL中数据的问题,题目表述太含糊,简单说,分3步工作:
1)找出SHEET1的"甲列"中相同的行,
2)把相同的行对应的"乙列"中的行求和SUM,
3)找出SHEET2的"丙列"中与"甲列"中相同的行,并把此行对应的"丁列"的值置为SUM.
  个人对EXCEL中的公式与函数不熟悉,又没有找到合适的人询问,只好自己在VBA里写代码.
因太久没有碰过VB,而且又不知道同学是否会再次喊我做同样的事情,所以把代码贴在这里,以备后用.

Sub SelectData()

    Dim i As Long, Max As Long, j As Long, Max2 As Long
    Dim count As Long
   
    Dim val As Double

    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim col11 As String, col12 As String, col13 As String, col21 As String, col22 As String
    col11 = "A"             '合并的列
    col12 = "C"             '累加数据列
    col13 = "A"             '与另一个SH2比较的列
   
    col21 = "B"             '与SH1比较的列
    col22 = "C"             '和数据存放的列

    Set sh1 = Sheet2
    Set sh2 = Sheet3  
    
    j = 1
    i = 1
    count = 1
    Max2 = 302
    Max = 252
    val = sh1.Range(col12 & 1).Value
   
    Do

        If sh1.Range(col11 & count).Value = sh1.Range(col11 & (i + 1)).Value Then

            val = val + sh1.Range(col12 & (i + 1)).Value
            i = i + 1

        Else
           
            For j = 1 To Max2
                If sh2.Range(col21 & j).Value = sh1.Range(col13 & (count)).Value Then
                    sh2.Range(col22 & j).Value = val
                    Exit For
                End If
               
            Next       
            
            i = i + 1
            count = i
            val = sh1.Range(col12 & i).Value                  
            
        End If

    Loop While i < Max              

End Sub

记:果然同学又有了新的功能,在原先的基础上修改就比较顺利了.

Sub SelectData()

    Dim i As Long, Max As Long, j As Long, Max2 As Long
    Dim count As Long
   
 
    Dim sh1 As Worksheet
    Dim col11 As String, col12 As String, col13 As String, col14 As String
    col11 = "A"             '合并的列
    col12 = "B"             '累加数据列
    col13 = "C"             '与另一个SH2比较的列
    col14 = "D"             '与另一个SH2比较的列
   

    Set sh1 = Sheet1
   
    j = 1
    i = 1
    count = 1
   
   
    Max2 = 10           'C列最大行数+1
    Max = 10            'A列最大行数
   
    Dim flg As Integer
   
    Do

            flg = 0
            For j = 1 To Max
                If sh1.Range(col11 & j).Value = sh1.Range(col13 & (count)).Value Then
                    sh1.Range(col14 & count).Value = sh1.Range(col12 & j).Value
                    flg = 1
                    Exit For
                End If
               
            Next
            If flg = 0 Then
                sh1.Range(col14 & count).Value = 0
            End If
            i = i + 1
            count = i
           
           
    Loop While i < Max2

End Sub