ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] À°¦£¸ÑÄÀ³o­Ó¼Ò½k¤ñ¹ï½d¨ÒÀÉ

[µo°Ý] À°¦£¸ÑÄÀ³o­Ó¼Ò½k¤ñ¹ï½d¨ÒÀÉ

¥i¥HÀ°§Ú»¡©úªþÀɤ¤ªºµ{¦¡½Xªº²[·N¶Ü?«Ü¦n¥Î¡A¦ý¬Ý¤£À´?©Ò¥H¤£ª¾¸Ó«ç¼Ë­×§ï¦¨¦Û¤v»Ý­nªº¡C

¼Ò½k¤ñ¹ï.rar (11.43 KB)
Jess

«Øij§A´£¥X¦Û¤vªº°ÝÃD
®³¥X§O¤Hªº½d¨Ò¤S¤£¾A¦X§A¥Î
¦Ó¥B­n§@ªÌ¦Û¤v¤~¯à¸ÑÄÀ¥Lªº«ä¸ô
§Ú¬O¬Ý¤£©ú¥Õ¥L¬°¤°»ò­n³o¼Ë¼g

TOP

¤£¦n·N«ä¡A¦]¬°³o­Ó½d¨ÒÀÉ©M§Ú¥Ø«e­n°µªº¤u§@´X¥G¨SÔ£®t§O¡A©Ò¥H¤~·|®³¨Ó½Ð±Ð¦U¦ì¡C
Jess

TOP

Sub test()
Dim a, b As Range, i%, j%, arr
a = Sheet1.Range(Sheet1.[a2], Sheet1.[b65536].End(3))
With Sheet2
Set b = .Range(.[a2], .[a65536].End(3))
For i = 1 To UBound(a)
arr = Filter(Application.Transpose(b), a(i, 1))
For j = 0 To UBound(arr)
b.Find(arr(j))(1, 2) = a(i, 2)
Next
Next
End With
End Sub
¬Û¦Pªº¥Øªº¡A¼gªk¦ó¤î¤d¦Ê¡A³o¼Ë¬O§_¸û©ö©ó²z¸Ñ¡H

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-5-9 09:05 ½s¿è

ªþÀɪºª`¸Ñ: ¦ý¤S ¤£·Q¥Î VBA ¨Ó¨Ï¥Î¦^°é³v­Ó§PÂ_¡AÁקKªá¶O®É¶¡¹Lªø¡A
¥i¬O ³B²z¸ê®Æ ¬OÁקK¤£¤F­n¥Î¨ì °j°éªº
ªþÀɪºµ{¦¡ ¥i­×§ï¦p¤U
  1. Sub Ex()
  2.     Dim Rng(1 To 2) As Range, R As Range
  3.     With Sheets("Sheet1")
  4.         Set Rng(1) = .Range(.[A2], .[A2].End(xlDown))   '³]©w¤ñ¹ïªºÄæ¦ì½d³ò
  5.         If Application.CountA(Rng(1)) = 0 Then Exit Sub  '¤ñ¹ïªº½d³ò¨S¸ê®Æ  Â÷¶}µ{¦¡
  6.     End With
  7.     With Sheets("Sheet2")
  8.         Set Rng(2) = .Range(.[A2], .[A2].End(xlDown))   '³]©wÅܧóªºÄæ¦ì½d³ò
  9.         If Application.CountA(Rng(2)) = 0 Then Exit Sub 'Åܧ󪺽d³ò¨S¸ê®Æ  Â÷¶}µ{¦¡
  10.         Rng(2).Offset(, 100) = Rng(2).Value             'Rng(2)¦ì²¾100Äæ¦ì¸mªº­È=Rng(2)ªº­È
  11.         Rng(2).Offset(, 1).FormulaR1C1 = "=IF(RC[99]=RC[-1],"""",RC[99])" 'Rng(2)¦ì²¾1Äæ¦ì¸mªº¼g¤U¤½¦¡
  12.     End With
  13.     For Each R In Rng(1)    '¨Ì§Ç³B¸Ì ¤ñ¹ï½d³òªºÀx¦s®æ
  14.         If R(1, 2) <> "" Then Rng(2).Offset(, 100).Replace "*" & R & "*", R(1, 2), Lookat:=xlPart
  15.         'Rng(2).Offset(, 100)¤¤ ¦³¥]§t RªºÀx¦s®æ §ó´«(Replace)¬°R(1, 2) [R¦ì²¾1Äæ]ªº¦r¦ê
  16.     Next
  17.     Rng(2).Offset(, 1).Value = Rng(2).Offset(, 1).Value
  18.     'Rng(2).Offset(, 1)ªº­È=Rng(2).Offset(, 1)ªº­È  'Rng(2).Offset(, 1)­ì¥»¬O¤½¦¡
  19.     Rng(2).Offset(, 100).Clear  '²M°£
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# jesscc


       ­è­è¤~¬Ý¨ì¦¹¤å³¹...

µy·L¬Ý¤F¤@¤UªþÀÉ..  ¦b ¼Ò²Õªº t20110308a01 ªº¤º®e¨ºÃä ´N¤w¸g¦³»¡©úÀÉ®×..
¥H¤U¬OÂ^¨ú¸Ó¤º®e
'¡Õ¦p¦ó¦b EXCEL ¤¤¥H "¼Ò½k¤ñ¹ï" ¨ú±o¬Û¹ïÀ³ªº¸ê®Æ¡Ö
'¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð
'¡½¤ñ¹ï¨Ó·½¸ê®ÆÁö¦³¨ä²Î¤@¤Î¿W¥ß©Ê½è¡A¦ý­n³Q¤ñ¹ïªº¸ê®Æ§e²{¤£³W«h§ÎºA¡A
   'ª©¤j §Æ±æ¥ÎÃþ¦ü LIKE ªº¤è¦¡¤ñ¹ï B LIKE A¡A
   '¦ý¤S ¤£·Q¥Î VBA ¨Ó¨Ï¥Î¦^°é³v­Ó§PÂ_¡AÁקKªá¶O®É¶¡¹Lªø¡A
   '¬O¦]¬°³o°ÝÃD¤£¾A¥Î LIKE ¨Ó³B²z¡A­Y¨Ì¦¹¼Ò¦¡¡A¥²¥ý¨ú¥X¤@¨Ó·½­È¡A¦A°j°é
   '³v¤@§PÂ_³Q¤ñ¹ï­È¡A¥ç§Y¦pªG¨Ó·½­È¦³ 100 ­Ó¡A³Q¤ñ¹ï­È¦³ 1,000 ­Ó¡A«h¶·
   '°j°é 100*1,000 ¦¸¡A·íµM¥¢¨ä®Ä¯q¡C
'¡½ÁÙ¦n¡A¦³ EXCEL ªº¡e¨ú¥N¡f¥\¯à¥i¨Ï¥Î¡A¯à¦³®Ä¦a³B²z³o°ÝÃD¡A¨ä¤èªk¬°¡G
   '±N¥þ³¡¡e³Q¤ñ¹ï­È¡f½Æ»s¦Ü¨ä¡e¬Û¹ï¦ì¸m¡fªº»²§UÄ椤¡A¦A³v¤@¥H¡e¨Ó·½­È¡f
   '¤ñ¹ï¡A¨ú¥N¬°±ý¨ú±oªº¡e¹ïÀ³­È¡f¡A§¹¦¨«á±N»²§UÄ檺µ²ªG¥N¤J¥Øªº°Ï¶ô¤¤¡C
   '¦¹¤è¦¡¡A¶È¥Î¨ì¨Ó·½­Èªº°j°é 100 ¦¸¡Aµ´¹ï¤ñ¤½¦¡¨Ó±o§Ö¦h¤F¡C
'¡½µM¦Ó¬J¬O¡e¼Ò½k¤ñ¹ï¡f¥[¡e¨ú¥N¡f¡A·Ç½T²vÃø«O¦Ê¤À¦Ê¡A¦b¦r¦ê¨ú¥Nªº¶¶§Ç
   '¤W­n¥H¸û¦h¦rªº¥ý¨ú¥N , ¨Ò¦p:
   'À³¥ý¨ú¥N¡e¤¤µØ¹q³q¡f¡A¦A¨ú¥N¡e¤¤µØ¹q¡f¡A¦A¨ú¥N¡e¤¤µØ¡f¡eµØ¹q¡f¡C
'¡½¤ñ¹ï¡e¨Ó·½­È¡f¤£¥i»P­n¨ú±oªºµ²ªG¡e¹ïÀ³­È¡f¦³¬Û¦P¦r¤¸¡A¥H§KÂù­«¨ú¥N¡C
'¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð

¤£¾å±o¼Ó¥D­nªº¬O«ç¼Ëªº®ÄªG
¥ú§â§O¤HªºÀÉ®×¥á¤W¨ÓµM«á»¡­n§ï ¤]¨S»¡­n§ï¤°»òµ²ªG
»¡¯uªº «ÜÃøÅý¤HÀ°±z ´N¹³¤W­±¤j¤j­Ì»¡ªº
¬Û¦P®ÄªG(µ²§½) ´N¦³¤£¦P¤è¦¡¥i¥H¥h°µ¸Ñ¨M
´N¦n¤ñ ±q¥x¥_­n¨ì«ÌªF  ´N¦³¦n´XºØ¤èªk¥i¥H¿ï¾Ü (®ü ³° ªÅ)
¦pªG ¿ï ®ü  ¨º ¤S¦³¤À §Ö ºC
¦pªG ¿ï ³° ¤S¦³¤À ¿¤¹D ¬Ù¹D ©Î °ª³t

¥H¤U¬Oµy·L§ó°Êªºµ{¦¡(§t§ó°Êªº¨Ç³\»¡©ú)
¦b¼Ò²Õªº T20110308a01 µ{¦¡¤U

Dim MyBook As Workbook, ShtA As Worksheet, ShtB As Worksheet, HeadA As Range, HeadB As Range, _
    RowsA As Long, RowsB As Long, bClmn(1 To 4) As Range, Ax(1 To 2), i, j  'bclmn(1 to N)   N= sheet1 ªº ¼Æ¶qªí¥Ü ¦p ¨ú®ø= 4

Sub ¦@¥Î°Ñ·Ó()
Set MyBook = ThisWorkbook
Set ShtA = MyBook.Sheets("Sheet1")
Set HeadA = ShtA.Range("A1")
With HeadA: RowsA = .Cells(65536 - .Row + 1, 1).End(xlUp).Row - .Row: End With
'-----------------------------------
Set ShtB = MyBook.Sheets("Sheet2")
Set HeadB = ShtB.Range("A1")
With HeadB: RowsB = .Cells(65536 - .Row + 1, 1).End(xlUp).Row - .Row: End With
End Sub

Sub ¶×¤J()
Call ¦@¥Î°Ñ·Ó
If RowsA <= 0 Then MsgBox "¡°¶×¤J¨Ó·½µL¶µ¥Ø¸ê®Æ!¡@¡@", 0 + 16: Exit Sub
If RowsB <= 0 Then MsgBox "¡°¶×¤J¥Ø¼ÐµL¶µ¥Ø¸ê®Æ!¡@¡@", 0 + 16: Exit Sub
Application.ScreenUpdating = False
'-----------------------------------------
Set bClmn(1) = Range(HeadB.Cells(2, 1), HeadB.Cells(RowsB + 1, 1))
Set bClmn(2) = Range(HeadB.Cells(2, 2), HeadB.Cells(RowsB + 1, 2))
Set bClmn(3) = Range(HeadB.Cells(2, 30), HeadB.Cells(RowsB + 1, 30))
Set bClmn(4) = Range(HeadB.Cells(2, 30), HeadB.Cells(RowsB + 1, 30)) 'set bclmn(N)   N= sheet1 ªº ¼Æ¶qªí¥Ü ¦p ¨ú®ø= 4 ¦p±ý¼W¥[¸Ó¼Æ¶qªí¥Ü «h¥²¶··s¼W1¦C

'-------------------------------------------
bClmn(3).Value = bClmn(1).Value
bClmn(2).FormulaR1C1 = "=IF(RC[28]=RC[-1],"""",RC[28])"
For i = 2 To RowsA + 1
    Ax(1) = HeadA.Cells(i, 1).Value
    Ax(2) = HeadA.Cells(i, 2).Value
    If Ax(1) <> "" And Ax(2) <> "" Then
       bClmn(3).Replace "*" & Ax(1) & "*", Ax(2), Lookat:=xlPart
    End If
Next i
'-----------------------------------------
bClmn(2).Value = bClmn(2).Value
bClmn(3).ClearContents
Beep
End Sub

TOP

¥»©«³Ì«á¥Ñ jesscc ©ó 2011-5-9 20:40 ½s¿è

«D±`·PÁ¦U¦ì¥ý¶iªº¼ö¤ß«ü¾É¡A¦U¦ì¤j¤jªº¥\¤O¤p§Ì¥u¯à»¡¨ØªA¡A¦ý¤p§Ì¯u¥¿ªº²~ÀV¡A¤´¥¼§JªA¡A§Ú­×§ï¤F¤@¤Usheet1¤¤ªº¸ê®Æ¡A¼W¥[¤F"¬ãµoA"¡B"¬ãµoB"¨Ã¦h¥[¤F¨â­ÓÄæ¦ì¡A¦p¦¹¤@¨Ósheet2¤¤¥u­n¦³"¬ãµo"¦r²´ªº¡A´N³£¤ñ¹ï¤£¨ì¤F¡A·Ó¹D²z¡A³o¨Ã¨S¦³¹H¤Ï¸ê®Æ®w¥¿³W¤Æ¸ê®Æ°ß¤@©Êªº­n¨D¡A¬OVBA¨S¦³³o¼Ëªº¬d¸ß¨ç¼Æ¶Ü?

¼Ò½k¤ñ¹ï­×§ï«á.rar (15.47 KB)
Jess

TOP

¥ý±q§Aªº»Ý¨D¶}©l¡A§A¬O­n¦bsheet2¤¤´M§ä¦³sheet1ªºAÄæ¦r¦ê
¤]´N¬O»¡Sheet2¤¤¦³¥]§tSheet1ªº¦r¦ê¡AµM«á±N¥N¸¹¡BÃþ§O¶ñ¨ìSheet2
¨º¸Õ°Ý¡A"¬ãµoA"¡B"¬ãµoB"¦bSheet2¤¤ªº¸ê®Æ§A¦p¦ó¯à§ä¨ì?
A8¤º®e"(100¦~3¤ë2¤é)¬ãµo1"
¥L¨Ã¥¼¥X²{"¬ãµoA"©Î"¬ãµoB"
©Ò¿×¼Ò½k¤ñ¹ï¬O»¡¦r¦ê¤¤¥]§tÃöÁä¦r¡AÁÙ¥i¯à¥X²{¨ä¥L¦r¤¸
´N¦]¬°§AªºÃöÁä¦r²{¦b¬O"¬ãµoA"©Î"¬ãµoB"
¨º»ò­n¦b´M§ä¦r¦ê¤¤¦³¥X²{"¬ãµoA"©Î"¬ãµoB"
¤~·|³Q»{¬°¤ñ¹ï¦¨¥\
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ jesscc ©ó 2011-5-9 22:39 ½s¿è

³á¡A¤£!sheet1¤~¬O¸ê®Æ®w¡C
¦]¬°sheet2ùتº¦r¦ê³£¤ñ¸ûªø¡A¤ñ¸û¶Ã¡A©Ò¥H§Ú¦b·Q¥u­n¦bsheet1ùؤñ¹ï¨ì¨ä¤¤´X­Ó³sÄò¦r¦ê³£²Å¦X¡A´N±N¬ÛÃö¸ê®Æ§ì¦^sheet2¡A¬O§Úªº·Qªk­è¦n¬Û¤Ï¶Ü?­ì©lÀɮ׬ݰ_¨Ó¹³¬O³o¼Ë¤ñ¹ïªº°Ú!

³o¼Ë»¡¦n¤F¡A¦pªGsheet2ùئ³¤@µ§¸ê®Æ"¬ãµoccab"¡A¦bsheet1ùØÀ³¸Ó¬O§ì"¬ãµoa"§a!
                  ¦pªGsheet2ùئ³¤@µ§¸ê®Æ"¬ãµoccba"¡A¦bsheet1ùØÀ³¸Ó¬O§ì"¬ãµob"§a!
¯Âºé­Ó¤H«ä¦Ò¡C
Jess

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-5-9 23:47 ½s¿è

"¬ãµoccba"«ç·|¬O¦³³sÄò¦r¦ê¬°"¬ãµob"©O?
³o¼Ë»¡§a
§A¬O­n¦b"¬ãµoccba"¤¤§ä¬Ý¬Ý¬O§_¦³"¬ãµoa"©Î"¬ãµob"
¨º§A§â"¬ãµoccba"¡A¨C3­Ó¦r¤Á³Î·|±o¨ì
"¬ãµoc"¡B"µocc"¡B"ccb"¡B"cba"
³o¸Ì­±·|¦³"¬ãµoa"©Î"¬ãµob"¶Ü?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD