¦p¦ó±N¦PÀɮפ¤¨âsheet§@¤ñ¸û¡A¦A±N¤ñ¸ûµ²ªG¥t¦s¨ä¥Lsheet¤¤?
- ©«¤l
- 6
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 8
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Win2000
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-5-23
- ³Ì«áµn¿ý
- 2013-8-13
|
¦p¦ó±N¦PÀɮפ¤¨âsheet§@¤ñ¸û¡A¦A±N¤ñ¸ûµ²ªG¥t¦s¨ä¥Lsheet¤¤?
vba¤p¹à©@¹J¨ì°ÝÃD¡A»Ýn±N¦PÀɮפ¤¨âsheet§@¤ñ¸û¡A¦A±N¤ñ¸ûµ²ªG
¥t¦s¨ä¥Lsheet¤¤¡C¦p¤U©Ò±Ô¡AÀµ½Ð¦U¦ì°ª¤â¤j¤jÀ°¦£¡C
thanks~
¦p¦ó¤ñ¹ïsheet2 & Sheet3¸ê®Æ¡A¨Ã±N¤ñ¹ïµ²ªG
¦s©ó¨ä¥Lsheet¤¤
1.¨âªÌ³£¦³-->sheet4
2.sheet2¦³¡Asheet3¨S¦³-->Sheet5
3.Sheet3¦³¡Asheet2¨S¦³-->Sheet6
shee2 ¸ê®Æ¦p¹Ïfig.1
sheet3 ¸ê®Æ¦p¹Ïfig.2 |
-
-
A.jpg
(15.38 KB)
fig.1
-
-
b.jpg
(25.1 KB)
fig.2
|
|
|
|
|
|
- ©«¤l
- 967
- ¥DÃD
- 0
- ºëµØ
- 0
- ¿n¤À
- 1001
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN XP
- ³nÅ骩¥»
- OFFICE 2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-11-29
- ³Ì«áµn¿ý
- 2022-5-17
|
¦^´_ 1# ckl520
³]sheet2~sheet6 ¤§AÄ欰P/N , BÄ欰Location- Sub XX()
- Dim d1 As Object, d2 As Object, A As Range
- Dim Ar, Br()
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- With Sheet2
- For Each A In .Range("A2:A" & .[A2].End(xlDown).Row)
- d1.Add A.Value, A.Offset(0, 1).Value
- Next
- End With
- With Sheet3
- For Each A In .Range("A2:A" & .[A2].End(xlDown).Row)
- d2.Add A.Value, A.Offset(0, 1).Value
- Next
- End With
- S = 1
- Ar = d1.keys
- ReDim Preserve Br(1 To d1.Count + d2.Count, 1 To 2)
- For I = LBound(Ar) To UBound(Ar)
- If d2.Exists(Ar(I)) Then
- Br(S, 1) = Ar(I): Br(S, 2) = d1(Ar(I)): d1.Remove (Ar(I)): S = S + 1
- Br(S, 1) = Ar(I): Br(S, 2) = d2(Ar(I)): d2.Remove (Ar(I)): S = S + 1
- End If
- Next I
- For I = 4 To 6
- Sheets(I).[A:B] = ""
- Sheets(I).[A1:B1] = Array("P/N", "Location")
- Next I
- Sheet4.[A2].Resize(UBound(Br, 1), 2) = Br
- Sheet5.[A2].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
- Sheet5.[B2].Resize(d1.Count, 1) = Application.Transpose(d1.items)
- Sheet6.[A2].Resize(d2.Count, 1) = Application.Transpose(d2.keys)
- Sheet6.[B2].Resize(d2.Count, 1) = Application.Transpose(d2.items)
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 6
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 8
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Win2000
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-5-23
- ³Ì«áµn¿ý
- 2013-8-13
|
register313 ¤j¤j §A¦n:
1.®M¥Î«á¡Aµ{¦¡·|¥d¦b³o¦æ Sheet4.[A2].Resize(UBound(Br, 1), 2) = Br
2.¤w¬d¹Lºô¤W¸ê®Æ¡A¦ýÁÙ¬O¬Ý¤£¤ÓÀ´¡AµLªk±Æ»Ù¡C¯à·Ð½Ð¤j¤j¤À¬q¸ÑÄÀµ{¦¡·N«ä¡C
¦A¦¸·PÁ°ª¤âÀ°¦£¡C
thanks~ |
|
|
|
|
|
|
- ©«¤l
- 967
- ¥DÃD
- 0
- ºëµØ
- 0
- ¿n¤À
- 1001
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN XP
- ³nÅ骩¥»
- OFFICE 2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-11-29
- ³Ì«áµn¿ý
- 2022-5-17
|
¦^´_ 3# ckl520
½Ð¤W¶ÇexcelÀ£ÁYÀÉ |
|
|
|
|
|
|
- ©«¤l
- 6
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 8
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Win2000
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-5-23
- ³Ì«áµn¿ý
- 2013-8-13
|
TO:register313
http://www.badongo.com/file/27212832
thanks~ |
|
|
|
|
|
|
- ©«¤l
- 967
- ¥DÃD
- 0
- ºëµØ
- 0
- ¿n¤À
- 1001
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN XP
- ³nÅ骩¥»
- OFFICE 2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-11-29
- ³Ì«áµn¿ý
- 2022-5-17
|
¦^´_ 5# ckl520
1.¤u§@ªícode name §ï¬° name
2.×¥¿ sheet4~sheet6 AÄæ ·|Åܬ°¤é´Á®æ¦¡¤§°ÝÃD- Private Sub CommandButton1_Click()
- Dim d1 As Object, d2 As Object, A As Range
- Dim Ar, Br() As String, Cr
- Set d1 = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- With Sheets("Sheet2")
- For Each A In .Range("A2:A" & .[A2].End(xlDown).Row)
- d1.Add A.Value, A.Offset(0, 1).Value
- Next
- End With
- With Sheets("Sheet3")
- For Each A In .Range("A2:A" & .[A2].End(xlDown).Row)
- d2.Add A.Value, A.Offset(0, 1).Value
- Next
- End With
- S = 1
- Ar = d1.keys
- ReDim Preserve Br(1 To d1.Count + d2.Count, 1 To 2)
- For I = LBound(Ar) To UBound(Ar)
- If d2.Exists(Ar(I)) Then
- Br(S, 1) = Ar(I): Br(S, 2) = d1(Ar(I)): d1.Remove (Ar(I)): S = S + 1
- d2.Remove (Ar(I)): S = S + 1
- End If
- Next I
- For I = 4 To 6
- Sheets("Sheet" & I & "").[A:B] = ""
- Sheets("Sheet" & I & "").[A1:B1] = Array("P/N", "Location")
- Sheets("Sheet" & I & "").[A:B].NumberFormatLocal = "@"
- Next I
- Sheets("Sheet4").[A2].Resize(UBound(Br, 1), 2) = Br
- Sheets("Sheet5").[A2].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
- Sheets("Sheet5").[B2].Resize(d1.Count, 1) = Application.Transpose(d1.items)
- Sheets("Sheet6").[A2].Resize(d2.Count, 1) = Application.Transpose(d2.keys)
- Sheets("Sheet6").[B2].Resize(d2.Count, 1) = Application.Transpose(d2.items)
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 6
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 8
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Win2000
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-5-23
- ³Ì«áµn¿ý
- 2013-8-13
|
ÁÂÁÂregister313 ¤j¤j
¥i¥Hwork¤F! |
|
|
|
|
|
|