- ©«¤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
|
¦^´_ 5# h60327 - Option Explicit
- Sub Ex()
- Dim Ar(), xi As Integer, xB As Integer, Rng As Range, xSh As Variant
- xB = 0 '°ÊºA°}¦C: ³Ì«á¤@ºû¤¸¯Àªº¼Æ¶q(xB)
- For Each xSh In Array("¤@²Õ", "¤G²Õ", "¤T²Õ") '°}¦C: ¶·³B¸Ìªº¤u§@ªí¦WºÙ
- With Sheets(xSh) '¥N¤J°}¦C¤¸¯À
- xi = 4 '³]©w¤u§@ªí¶}©lªºÀx¦s®æªº¦C¼Æ
- Do While .Cells(xi, "B") <> "" '°õ¦æ°j°éDo Loopªº±ø¥ó: BÄ檺Àx¦s®æ <> ""
- If .Cells(xi, "Q") >= 6 Then 'QÄ檺Àx¦s®æ>=6
- ReDim Preserve Ar(1 To 6, xB) '°ÊºA°}¦C: ReDim(½Õ¾ã) ³Ì«á¤@ºû¤¸¯Àªº¼Æ¶q(xB)
- 'Preserve ¿ï¾Ü©Ê¤Þ¼Æ¡C·í§ïÅÜ즳°}¦C³Ì«á¤@ºûªº¤j¤p®É¡A¤´µM«O¦³ì¨Óªº¸ê®ÆªºÃöÁä¦r¡C
- Set Rng = Sheets("¤H¸ê").Cells.Find(.Cells(xi, "B")) 'Sheets("¤H¸ê")¤¤´M§ä .Cells(xi, "B") :
- Ar(1, xB) = Rng.Cells(1, 2) '¼g¤J:©m¦WÄ橹¥k2Äæ
- Ar(2, xB) = Rng.Cells(1, 3)
- Ar(3, xB) = Rng '¼g¤J:©m¦WÄæ
- Ar(4, xB) = Rng.Cells(1, 4)
- Ar(5, xB) = "100¤U¥b¦~ÀuÂI¹F" & IIf(.Cells(xi, "Q") >= 12, "12", "6") & "¦¸¡A¨¯³Ò±o¤O"
- Ar(6, xB) = "¹Å¼ú" & IIf(.Cells(xi, "Q") >= 12, "¶L¦¸¡]4002¡^", "³ü¦¸¡]4001¡^")
- xB = xB + 1 '³Ì«á¤@ºû¤¸¯Àªº¼Æ¶q(xB)
- End If
- xi = xi + 1 '¨ÏBÄ檺Àx¦s®æ©¹¤U¤@¦C²¾°Ê
- Loop
- End With
- Next
- If xB <> 0 Then '°ÊºA°}¦C¦³¤¸¯À¦s¦b
- With Sheets("¥»®×¼úÃg«Øij¦W¥U")
- .Range(.[a3], .[a3].End(xlDown)).Resize(, 6) = "" '²M°£ì¦³¤º®e
- .[a3].Resize(xB, 6) = Application.Transpose(Ar) ' Transpose : Âà¸m(¤u§@ªí¨ç¼Æ)
- End With
- MsgBox "²Å¦Xªº¸ê®Æ ¦@ " & xB & "µ§"
- Else
- MsgBox "¬dµL ²Å¦Xªº¸ê®Æ"
- End If
- End Sub
½Æ»s¥N½X |
|