1.VBA实现EXCEL中Sheet1的 "甲列" 相同数值的行对应的"乙列"的数的和作为Sheet2中"丙列"中与Sheet1中"甲列" 相同的行对应的"丁列"的值
今天有一同学,喊我帮忙处理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
COPYRIGHT©2008,HTTP://ZEROBUG.CNBLOGS.COM .ALL RIGHTS RESERVED.