¡@¼ÊºÙ: ÀH·ÄÆ¿ºªº¦Ð¤ò ¡@ÀY»Î: [±s¥Î]¼ç¤ô¸¥
°ª¤¤¥Í
- ©«¤l
- 852
- ¥DÃD
- 79
- ºëµØ
- 0
- ¿n¤À
- 918
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7 , XP
- ³nÅ骩¥»
- Office 2007, Office 2003,Office 2010,YoZo Office
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¦t©z
- µù¥U®É¶¡
- 2011-4-8
- ³Ì«áµn¿ý
- 2024-2-21
|
¦^´_ 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¡AY¨Ì¦¹¼Ò¦¡¡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ªº¶¶§Ç
'¤Wn¥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»Pn¨ú±oªºµ²ªG¡e¹ïÀ³È¡f¦³¬Û¦P¦r¤¸¡A¥H§KÂù«¨ú¥N¡C
'¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð
¤£¾å±o¼Ó¥Dnªº¬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 |
|