- ©«¤l
- 12
- ¥DÃD
- 0
- ºëµØ
- 0
- ¿n¤À
- 62
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- office 365
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2022-2-1
- ³Ì«áµn¿ý
- 2022-12-29
|
¦^´_ 5# Andy2483
¸Õ¸Õ¶i¶¥¿z¿ï- Option Explicit
- Dim Rng(1 To 5) As Range
- Sub ¶µ¬Û¤ÀÃþ«¾ã()
- ³]©w
- ¶i¶¥¿z¿ï
- End Sub
- Private Sub ³]©w()
- Set Rng(1) = Sheets("¤ÀÃþ±b").Range("A1").CurrentRegion
- With Sheets("µ²ªG")
- .Cells.Clear
- Set Rng(2) = .[Z1] '¦s©ñ©ú²Ó¬ì¥Ø_¹ô§OÄd ¤£«´_ªº¶µ¥Ø
- Set Rng(5) = .[A1] '¦s©ñ¨C¦¸¿z¿ïªº¦ì¸m
- End With
- Rng(1).Range("A1").Copy Rng(2)
- Rng(1).AdvancedFilter xlFilterCopy, Rng(2).Cells.Resize(2), Rng(2), True ' "©ú²Ó¬ì¥Ø_¹ô§O"¿z¿ï¤£«´_ªº¶µ¥Ø
- Set Rng(3) = Rng(2).Offset(, 1) '¿z¿ïªº·Ç«h½d³ò
- Rng(1).Range("A1,D1").Copy Rng(3) '·Ç«hªºÄæ¦ì
- Set Rng(4) = Rng(3).Offset(, 3) '«ü©w³Q½Æ»s¦Cªº¥Ø¼Ð½d³ò
- End Sub
- Private Sub ¶i¶¥¿z¿ï()
- Dim i As Integer, R As Range
- Rng(3).Range("B2") = "=" & """<>" & " ¥» ¤é ¦X p""" '¶ñ[ºKn]¤J·Ç«h ±ø¥ó
- i = 2
- Do While Rng(2).Cells(i) <> ""
- Rng(4).CurrentRegion.Clear
- Rng(3).Range("A2") = Rng(2).Cells(i) '¶ñ¤J[©ú²Ó¬ì¥Ø_¹ô§O]·Ç«h±ø¥ó
- Rng(1).AdvancedFilter xlFilterCopy, Rng(3).Resize(2, 2), Rng(4) '¶i¶¥¿z¿ï'
- Rng(5) = Rng(2).Cells(i).Value '¼ÐÀY ¿z¿ïªº[©ú²Ó¬ì¥Ø_¹ô§O]
- Rng(4).CurrentRegion.Offset(, 1).Copy Rng(5).Offset(1) '¸ê®Æªº½d³ò .Offset(, 1) ¦V¥k²¾°Ê¤@Äd **¤£»Ýn [©ú²Ó¬ì¥Ø_¹ô§O]Äæ
- Set Rng(5) = Rng(5).End(xlDown).Offset(2)
- i = i + 1
- Loop
- End Sub
½Æ»s¥N½X |
|