¡mµo°Ý¡nvba-¦W³æ¤ñ¹ï¬Û²Å¦X¦^¼g¸ê®Æ
©«¤l 77 ¥DÃD 30 ºëµØ 0 ¿n¤À 110 ÂI¦W 0 §@·~¨t²Î win7 ³nÅ骩¥» office2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2013-7-14 ³Ì«áµn¿ý 2018-10-3
¡mµo°Ý¡nvba-¦W³æ¤ñ¹ï¬Û²Å¦X¦^¼g¸ê®Æ
½Ð±Ð¦U¦ì°ª¤â¡AÃö©ó¤ñ¹ï¦W³æªºvba¼gªk¡A¥un²Å¦X¡m¸ê®Æ¨Ó·½¡nªºAÄæ¦ì²Å¦X¡m¦W³æ¡nºP¶ªº¤H¦W¡A§Y±N¯S©wªºÄæ¦ì¦^¼g¦b¥t¤@Ó¤u§@¶¡]¤ñ¹ï«á¸ê®Æ¡^¡A
¥Ñ©ó¸ê®Æ¨Ó·½¨C¤é·|¦³¼Æ¤dµ§¡A©Ò¥H¡A§Ú¼g¤F²³æªºµ{¦¡½X«á¡A³q±`³£nRUN«Ü¤[~¤£ª¾¹D¦³¤°»ò¤èªk¥i¥H¦A§Ö¨Ç¡I
Sub ¤ñ¹ï¦W³æ()
Sheets("¤ñ¹ï«á¸ê®Æ").Select
Cells.Clear
Sheets("¸ê®Æ¨Ó·½").Select
k = Sheets("¸ê®Æ¨Ó·½").Range("a65536").End(xlUp).Row
p = Sheets("¦W³æ").Range("a65536").End(xlUp).Row
Sheets("¤ñ¹ï«á¸ê®Æ").Cells(1, 1).Value = Sheets("¸ê®Æ¨Ó·½").Cells(1, 1)
Sheets("¤ñ¹ï«á¸ê®Æ").Cells(1, 4).Value = Sheets("¸ê®Æ¨Ó·½").Cells(1, 3)
Sheets("¤ñ¹ï«á¸ê®Æ").Cells(1, 5).Value = Sheets("¸ê®Æ¨Ó·½").Cells(1, 4)
c = 2
For n = 2 To k
For m = 1 To p
If Sheets("¸ê®Æ¨Ó·½").Cells(n, 1) = Sheets("¦W³æ").Cells(m, 1) Then
Sheets("¤ñ¹ï«á¸ê®Æ").Cells(c, 1).Value = Sheets("¸ê®Æ¨Ó·½").Cells(n, 1)
Sheets("¤ñ¹ï«á¸ê®Æ").Cells(c, 3).Value = Sheets("¸ê®Æ¨Ó·½").Cells(n, 3)
Sheets("¤ñ¹ï«á¸ê®Æ").Cells(c, 4).Value = Sheets("¸ê®Æ¨Ó·½").Cells(n, 4)
c = c + 1
End If
Next
Next
End Sub
Book1.rar
(1.65 KB)
Book1.rar
(1.65 KB)
·s¤H¤@ªT
©«¤l 77 ¥DÃD 30 ºëµØ 0 ¿n¤À 110 ÂI¦W 0 §@·~¨t²Î win7 ³nÅ骩¥» office2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2013-7-14 ³Ì«áµn¿ý 2018-10-3
¦^´_ 2# Hsieh
·PÁ¡I§Ú®M¤J«á¡A¸ê®Æ¨Ó·½²Å¦X¦W³æªº¥u·|¥X²{¤@¦¸¡A¦pªG¡A¸ê®Æ¨Ó·½¤¤¡A¤H¦W¥X²{¦h¦¸¤]·|¦P¼Ë·|±a¤J¤ñ¹ï¸ê®ÆªººP¶¡An«ç»ò°µ¡H
¤£¦n·N«ä¡A§Úèè¨S¦³ªí¹F²M·¡¡I
·s¤H¤@ªT
©«¤l 77 ¥DÃD 30 ºëµØ 0 ¿n¤À 110 ÂI¦W 0 §@·~¨t²Î win7 ³nÅ骩¥» office2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2013-7-14 ³Ì«áµn¿ý 2018-10-3
[ª©¥DºÞ²z¯d¨¥] Hsieh(2014-1-16 23:53): ³o¨Ç³£¬O°ò¥»°ÝÃD¡A½×¾Â¤¤³£°Q½×¹L¡A·s¤â´N½Ð¦hª¦¤å
ÁÂÁ¡I¥i¥H®M¤J¤F¡I¦ý¬O
¥i½Ð±Ð´XÓ°ÝÃD¶Ü¡H¡]§Úªì¤Jªù¤£¤Ó¤F¸Ñ¡A©Ò¥H°Ý¤F²Â°ÝÃD¡^
For Each A In .Range(.[A2], .[A2].End(xlDown))
¬°¤°»ò .Range«e±·|¦³ÓÂI¡A¦Ó(.[A2], .[A2].¤]³£·|¦³ÓÂI
If Not Rng Is Nothing Then ³o¥yµ{§Ç½Xªº·N«ä¡H
s& ³o¬O©w¸q¤°»ò¡H
¦pªG¡A§Ú·Q¨úªº¸ê®Æ¨Ó·½Y¤£¬O³sÄòÄæ¦ì¡H¡]¥i¯à¬OA¡BB¡BC¡BG¡BIÄæ¡^¬O¤£¬O´N¤£¥i¥H¥Î
Ar(s) = Application.Transpose(Application.Transpose(c.Resize(, 4).Value))
·s¤H¤@ªT
©«¤l 5923 ¥DÃD 13 ºëµØ 1 ¿n¤À 5986 ÂI¦W 0 §@·~¨t²Î win10 ³nÅ骩¥» Office 2010 ¾\ŪÅv 150 ©Ê§O ¨k ¨Ó¦Û ¥xÆW°ò¶© µù¥U®É¶¡ 2010-5-1 ³Ì«áµn¿ý 2022-1-23
¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-1-17 10:11 ½s¿è
¦^´_ 5# eric093
¦³¤£¸Ñ¤§³B ½×¾Â¤¤·j´MÃöÁä¦r,¦h¬Ý¬Ý·|¶i¨Bªº.
¥t¤@¼gªk¨Ñ°Ñ¦ÒOption Explicit
Sub ¤ñ¹ï¦W³æ()
Dim Rng(1 To 3) As Range, E As Range
Sheets("¤ñ¹ï«á¸ê®Æ").UsedRange.Offset(1).Clear ''²Ä¤@¦Cªº ©m¦W,©~¦í¦a,©Ê§O,¦~ÄÖ,¤£²M°£
Set Rng(1) = Sheets("¸ê®Æ¨Ó·½").Range("A:A") '¸ê®Æ¨Ó·½¦W³æ
Set Rng(2) = Sheets("¦W³æ").Range("A2") '²Ä¤@Ó¤H¦W
Do While Rng(2) <> ""
Set Rng(3) = Rng(1).Find(Rng(2), lookat:=xlWhole) '¸ê®Æ¨Ó·½¦W³æ¤¤·j´M¤H¦W
If Not Rng(3) Is Nothing Then 'Not Rng Is Nothing :¦³§ä¨ì¤H¦W
Rng(1).Replace Rng(2), "=gbkee", xlWhole '±N¬Û¦Pªº¤H¦W´À´«¬°¿ù»~È
With Rng(1).SpecialCells(xlCellTypeFormulas, xlErrors) '¯S®íªº½d³ò(¤½¦¡,¿ù»~È)
.Value = Rng(2) '¿ù»~ȧï¦^¤H¦W
For Each E In .Cells 'Each E : ¤@Ó°}¦C©Î¶°¦X¤¤ªº¨C¤@¤¸¯À©Î¦¨û
With Sheets("¤ñ¹ï«á¸ê®Æ")
'E.Resize(, 4).Copy .Cells(.UsedRange.Rows.Count + 1, "A") '³sÄòªº4Äæ
'¥i¯à¬OA¡BB¡BC¡BG¡BIÄæ (¤£³sÄò5Äæ)
.Cells(.UsedRange.Rows.Count + 1, "A").Resize(, 5) = Array(E, E.Range("B1"), E.Range("C1"), E.Range("G1"), E.Range("I1"))
End With
Next
End With
End If
Set Rng(2) = Rng(2).Offset(1) '¤U¤@Ó¤H¦W
Loop
End Sub ½Æ»s¥N½X
©«¤l 182 ¥DÃD 34 ºëµØ 0 ¿n¤À 316 ÂI¦W 0 §@·~¨t²Î Win 7 ³nÅ骩¥» Office 2010 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û Taiwan µù¥U®É¶¡ 2010-5-2 ³Ì«áµn¿ý 2023-6-24
¦^´_ 6# GBKEE
Dear GBKEE
¦pªG¡ª¸ê®Æ¨Ó·½¡ªAÄ欰 12½X½s¸¹¡]¦p¡G1001QWER7895¡^
¡ª¦W³æ¡ª²Å¦X¨ä¤¤¤T½X¡]¦p¡GQWE¡^¡A½s¸¹²Ä5~7¤T½X©Î¥ô¦ó¦ì¸m¤T½X¡C
µ{¦¡½X¦p¦ó×q
·PÁ«ü¾É
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X
©«¤l 5923 ¥DÃD 13 ºëµØ 1 ¿n¤À 5986 ÂI¦W 0 §@·~¨t²Î win10 ³nÅ骩¥» Office 2010 ¾\ŪÅv 150 ©Ê§O ¨k ¨Ó¦Û ¥xÆW°ò¶© µù¥U®É¶¡ 2010-5-1 ³Ì«áµn¿ý 2022-1-23
¦^´_ 7# b9208 Option Explicit
Option Base 1
Sub ¤ñ¹ï¦W³æ()
Dim Ar(1 To 2), Ax(), E As Variant, i As Integer, S As Integer
Ar(1) = Application.Transpose(Sheets("¦W³æ").UsedRange.Columns(1)) '¦W³æªº¸ê®ÆÂà¤J°}¦C
Ar(2) = Sheets("¸ê®Æ¨Ó·½").UsedRange '¸ê®Æ¨Ó·½ªº¸ê®ÆÂà¤J°}¦C
S = 1
For Each E In Ar(1) '*** ½Ð×¥¿ ¦W³æªº¼ÐÀY= ¸ê®Æ¨Ó·½:¦W³æªº¼ÐÀY
For i = 1 To UBound(Ar(2))
If InStr(Ar(2)(i, 1), E) Then '¦³¤ñ¹ï¨ì>0 ±ø¥ó¦¨¥ß
'InStr ¨ç¼Æ ¶Ç¦^¦b¬Y¦r¦ê¤¤¤@¦r¦êªº³Ì¥ý¥X²{¦ì¸m¡A¦¹¦ì¸m¬° Variant (Long)¡C
ReDim Preserve Ax(S)
Ax(S) = Application.Index(Ar(2), i) '¨ú³sÄòÄæ¦ì
'******* ¨ú¤£³sÄòªºÄæ¦ì 'A.B.C.E.G,H->1,2,3,5,7,8
'Ax(S) = Array(Ax(S)(1), Ax(S)(2), Ax(S)(3), Ax(S)(5), Ax(S)(7), Ax(S)(8))
S = S + 1
End If
Next
Next
Sheets("¤ñ¹ï«á¸ê®Æ").UsedRange.Clear '¥þ³¡²M°£
Sheets("¤ñ¹ï«á¸ê®Æ").[a1].Resize(UBound(Ax, 1), UBound(Ax(1))) = Application.Transpose(Application.Transpose(Ax))
End Sub ½Æ»s¥N½X
©«¤l 182 ¥DÃD 34 ºëµØ 0 ¿n¤À 316 ÂI¦W 0 §@·~¨t²Î Win 7 ³nÅ骩¥» Office 2010 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û Taiwan µù¥U®É¶¡ 2010-5-2 ³Ì«áµn¿ý 2023-6-24
¦^´_ 8# GBKEE
«D±`·PÁª©¤j
µ{¦¡½X¥i¥H¹B¥Î°õ¦æ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X
©«¤l 77 ¥DÃD 30 ºëµØ 0 ¿n¤À 110 ÂI¦W 0 §@·~¨t²Î win7 ³nÅ骩¥» office2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2013-7-14 ³Ì«áµn¿ý 2018-10-3
ÁÂÁª©¥D´£¿ô¤Î«ü¾É¡A¤]ÁÂÁ GBKEE¡I§Ú·|¦A¥[ªoªº¡A¨C¤Ñ»{¯uª¦¤åªº¡I
¯uªº«Ü·PÁ¡I
·s¤H¤@ªT