5.EXCEL中的VBA代码

今天有一同学,喊我帮忙处理EXCEL中数据的问题,题目表述太含糊,简单说,分3步工作:ITPUB个人空间1` F^#M |1c%S)b
1)找出SHEET1的"甲列"中相同的行,ITPUB个人空间x?`fBmPO+E
2)把相同的行对应的"乙列"中的行求和SUM,
qdHBF)s1y`;`03)找出SHEET2的"丙列"中与"甲列"中相同的行,并把此行对应的"丁列"的值置为SUM.
DfC%bc*u L0  个人对EXCEL中的公式与函数不熟悉,又没有找到合适的人询问,只好自己在VBA里写代码.ITPUB个人空间I(BA#wF-?0n)D9N7q
因太久没有碰过VB,而且又不知道同学是否会再次喊我做同样的事情,所以把代码贴在这里,以备后用.ITPUB个人空间)S3R&@/~
c-H L

1|-H l,p#R7B0Sub SelectData()

    Dim i As Long, Max As Long, j As Long, Max2 As LongITPUB个人空间,q2|I TO#X
    Dim count As Long
q)eM2Yk!aq2c0   ITPUB个人空间k{HdR9P
    Dim val As DoubleITPUB个人空间`#g7pf']2n)p CQK
ITPUB个人空间x
O7W ~[r
    Dim sh1 As Worksheet, sh2 As WorksheetITPUB个人空间F4|;~nH
    Dim col11 As String, col12 As String, col13 As String, col21 As String, col22 As StringITPUB个人空间(ESRXmcX,^ g-jF
    col11 = "A"             '合并的列ITPUB个人空间Y+ci~#EGX-R&h.f9zi
    col12 = "C"             '累加数据列ITPUB个人空间7S*}'N9~DrX@E9_
    col13 = "A"             '与另一个SH2比较的列
;K%G$yzb[
d0   
[_1}Y#Ax0    col21 = "B"             '与SH1比较的列
(agJ$l%Q0    col22 = "C"             '和数据存放的列

    Set sh1 = Sheet2
#t/YU.H.GNr0    Set sh2 = Sheet3  ITPUB个人空间VWR"ySqLW|
    ITPUB个人空间 PZ0A&Ka5s$n
    j = 1
EAVQSI%l;d0    i = 1ITPUB个人空间.wCtYy3p3P/y"YP
    count = 1
;S\%M6m6A?0    Max2 = 302ITPUB个人空间{pj usF$y6O4q2q
    Max = 252
5c-j$S.x/@8XT;|0    val = sh1.Range(col12 & 1).Value
%^1t a4r7Pf#zM0   ITPUB个人空间7tu]5T2Hw
    Do

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

            val = val + sh1.Range(col12 & (i + 1)).Value
7`]k!j.b ?}0            i = i + 1

        Else
Y \I3v,H H3_#l0           ITPUB个人空间b6},@cMKc
            For j = 1 To Max2
M4{6kn-l3p
Ul$l0                If sh2.Range(col21 & j).Value = sh1.Range(col13 & (count)).Value Then
cYb|2Y5MN1]0                    sh2.Range(col22 & j).Value = valITPUB个人空间nQM8Rbs9K'`"f
                    Exit ForITPUB个人空间2b(^Am OX$t
                End IfITPUB个人空间r.^?X {4mm:n
               
W!E']g+aRH2Tv0            Next       ITPUB个人空间x/M(}J&r
            
"Z6IYr0iI#d1L `0            i = i + 1ITPUB个人空间{W$[4RZ~ k
            count = iITPUB个人空间)n&f3B#aFI
            val = sh1.Range(col12 & i).Value                  
Ct8R qt$Y0            ITPUB个人空间jm*MO+o)e
        End If

    Loop While i < Max              

End Sub

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

Sub SelectData()

    Dim i As Long, Max As Long, j As Long, Max2 As LongITPUB个人空间/L[x G,`
    Dim count As Long

cd5r,I:[,PP7Ht~0   ITPUB个人空间.}"JD(`5@@,a Em7O
 ITPUB个人空间$KoUNn{u
    Dim sh1 As WorksheetITPUB个人空间8`7L*FIEh
    Dim col11 As String, col12 As String, col13 As String, col14 As StringITPUB个人空间9T S&wDM2T)c
    col11 = "A"             '合并的列ITPUB个人空间R_)Nb$}pO)V
    col12 = "B"             '累加数据列ITPUB个人空间f;W!M5HJF:X
    col13 = "C"             '与另一个SH2比较的列
gM#x [!]!@ H}@0    col14 = "D"             '与另一个SH2比较的列ITPUB个人空间 M mRc3tWb
   

    Set sh1 = Sheet1
BM)~8E$r3P%^ _M!w0   ITPUB个人空间 N MNw-K6~
    j = 1
k#u1V*sOd5M0\0    i = 1ITPUB个人空间`MP"^Vls2\7X$y
    count = 1
%POn%g C\0   
%RbE'@#M.T$T#@+V0   
+Om)zcE?:` H&}?4Z0    Max2 = 10           'C列最大行数+1
{&FRK9Mi0    Max = 10            'A列最大行数ITPUB个人空间(X9`6ep(nY@7UI
   
G$s5Od w-p6N!A0    Dim flg As Integer
b(@/Av(z3D s3u0   ITPUB个人空间!r*T
t
LJK$@8V
    Do

            flg = 0
N[z,`*A)q s#M _;]0            For j = 1 To Max
_@;? mM;IT+]0                If sh1.Range(col11 & j).Value = sh1.Range(col13 & (count)).Value Then
!t6aZ`K8q4M2N3@0                    sh1.Range(col14 & count).Value = sh1.Range(col12 & j).ValueITPUB个人空间`tku"X0By^3a
                    flg = 1ITPUB个人空间 G9L"k8hM
kv
                    Exit For
4Tl$~3B!G0                End If
%KK/`$Hh.D5?0               
T T6dC ksw0            NextITPUB个人空间'B XowzB
            If flg = 0 Then
Rpkl)dzy's0                sh1.Range(col14 & count).Value = 0
4`9`,F XQ Z0            End IfITPUB个人空间2x0]H8x3_O!_!n Bb
            i = i + 1
8B9g.}S*T7T@n0            count = iITPUB个人空间$jvJTD"Wq%?
           ITPUB个人空间Z(z:s;e4S |4|
           
4kF&xr2r.`5q1Db ~0    Loop While i < Max2

End Sub

4fw7u]Xgx_0