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

[µo°Ý] vba for next¥Îªk¤Î®Ä²v§ïµ½°ÝÃD

[µo°Ý] vba for next¥Îªk¤Î®Ä²v§ïµ½°ÝÃD

¤§«e¦³µo°Ý¡A¦ý¬O¦³´X­Ó¦a¤è§Ú§Ë¿ù¤F¡A©Ò¥H­«·s´£°Ý¤@¹M¡A³Â·Ð¤j®aÀ°§Ú·Q¤@·Q!!
µ{¦¡½X
  1. Sub morecriteriafilter()
  2.    
  3.     Dim i As Integer, j As Integer, k As Integer
  4.    
  5.     With Worksheets("be1")
  6.     x = 0
  7.         For i = 0 To 5
  8.             For j = 0 To 5
  9.                     .Range("A1").AutoFilter Field:=2, Criteria1:="<" & 3 + i, Operator:=xlAnd, Criteria2:=">" & 0 + i
  10.                     .Range("A1").AutoFilter Field:=3, Criteria1:="=" & 1 + x
  11.                     .Range("A1").AutoFilter Field:=5, Criteria1:=Cells(j + 2, 10)
  12.                     .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Worksheets("1").Range("B2").Offset(7 * i, 7 * j)
  13.                     
  14.             Next j
  15.         Next i
  16.     .Range("A1").AutoFilter
  17.     End With
  18.    
  19. End Sub
½Æ»s¥N½X
Åܼƽd³ò
t0 (field:=2) : 1~1065
T (field:=3) : 1~121
K (field:=5) : Cells(2~132,10)
°ÝÃD¦p¤U
¥Ø«eµ{¦¡½X°²©wT=1(§Yx=0)¡A§Ú³]©w¤Fi©Mj¨Óªí¥ÜÅܼÆt0¡BK¤£¦P®É¡A·|¦b¤u§@ªí"1"¤¤¤£¦P¦ì¸mÅã¥Ü
Q1:   ¦ý¬O§Ú²{¦b·Q¥[¤F¤@­Ófor x=0 to 120¨Óªí¥ÜÅܼÆT¤£¦P®Éªº°j°é¡A¨ÃÅã¥Ü¦bÃB¥~¼W¥[(¥»¨Ó¨S¦³)ªº¤u§@ªí"2"¡B"3"¡B...¡B"120"¡B"121"¤¤¡A¦ý¬O.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Worksheets("1").Range("B2").Offset(7 * i, 7 * j)¤¤ªºWorksheets("1")¤£ª¾¹D­n¦p¦ó§ï?
Q2:  §Ú´£¨Ñªºµ{¦¡½X¥uÅã¥Üi =0 to 5 ¤Îj= 0 to 5¡A¦ý¬O§Ú­nªº¬Oi=0 to 1063¡Aj=0 to 130¡A¥i¬O·|·í¾÷¡A·Q½Ð°Ý¦p¦ó§ï°Ê?
dd.png
2016-5-9 11:02

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-5-10 22:32 ½s¿è

¦^´_ 1# ³Q½×¤å¹GºÆªº¤H
Q1:

Worksheets("1") => Sheets(x + 1 & "")


Q2:
¸Õ¸Õ³o¼Ë¬O§_¥i¥H: (¥¼¦Ò¼{®Ä²v§ïµ½, ¶È°w¹ï·í¾÷±¡§Î³B²z)
  1. Sub morecriteriafilter()
  2.    
  3.     Dim i As Integer, j As Integer, k As Integer
  4.    
  5.     With Worksheets("be1")
  6.     x = 0
  7.         Application.ScreenUpdating = False
  8.         For i = 0 To 5
  9.             For j = 0 To 5
  10.                     .Range("A1").AutoFilter Field:=2, Criteria1:="<" & 3 + i, Operator:=xlAnd, Criteria2:=">" & 0 + i
  11.                     DoEvents
  12.                     .Range("A1").AutoFilter Field:=3, Criteria1:="=" & 1 + x
  13.                     DoEvents
  14.                     .Range("A1").AutoFilter Field:=5, Criteria1:=Cells(j + 2, 10)
  15.                     DoEvents
  16.                     .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Worksheets("1").Range("B2").Offset(7 * i, 7 * j)
  17.                     DoEvents
  18.             Next j
  19.         Next i
  20.     .Range("A1").AutoFilter
  21.     DoEvents
  22.     Application.ScreenUpdating = True
  23.     End With
  24. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# luhpro
¤È¦w~~
«Ü·PÁ±zªºÀ°§U!!!
§Ú¹Á¸Õ¥Î¤F±z´£¨Ñªº¨â­Ó¤èªk
°µ¤F¤@¨Ç§ó°Ê
´N¦¨¥\¤F!!!
¥H¤U¬Oµ{¦¡½X:¡G
  1. Sub test()
  2.    
  3.     Dim i As Integer, j As Integer, x As Integer
  4.    
  5.     With Worksheets("test")
  6.     Application.ScreenUpdating = False
  7.     For x = 2 To 48
  8.         For i = 0 To 1063
  9.             For j = 0 To 142
  10.                     .Range("A1").AutoFilter Field:=2, Criteria1:="<" & 3 + i, Operator:=xlAnd, Criteria2:=">" & 0 + i
  11.                     DoEvents
  12.                     .Range("A1").AutoFilter Field:=3, Criteria1:=Cells(x, 11)
  13.                     DoEvents
  14.                     .Range("A1").AutoFilter Field:=5, Criteria1:=Cells(j + 2, 10)
  15.                     DoEvents
  16.                     .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Sheets(x).Cells(2, 2).Offset(7 * i, 7 * j)
  17.                         '¸ÑÄÀ-----.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy«á­±©ñªºsheets(x)¡A
  18.                         'µo²{()¤º¤£¯à¼g¦¨x+1ºâ¦¡¡A·|¶]¥X¦Psheets(1)ªº­È(¦¹®ÉxÀq»{¬°0,´Nºâ§Ú¥ý³]x=2¤]¤@¼Ë)¡F
  19.                         '¨Ï¥Îsheets(x)®É¡A¦pªG¦³¨Æ¥ý³]¸mx=2,´N·|¶]¥Xsheets(2)ªº­È¡A©Ò¥H§Ú´N¥Î³o­Ó¡C
  20.                     DoEvents
  21.             Next j
  22.         Next i
  23.     Next x
  24.     .Range("A1").AutoFilter
  25.     Application.ScreenUpdating = True
  26.     '¨Ï¥Îdoevents©Mapplication.screenupdating«á§Úµo²{¾ãÅé®Ä²v·|¤U­°¡A¦ý¬O¯uªº¯à§ïµ½·í¾÷°ÝÃD
  27.    
  28.     End With
  29.    
  30. End Sub
½Æ»s¥N½X
Thank you very much^  ^

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-5-13 22:19 ½s¿è
¦^´_  luhpro
...
'¸ÑÄÀ-----.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy«á­±©ñªºsheets(x)¡A
'µo²{()¤º¤£¯à¼g¦¨x+1ºâ¦¡¡A·|¶]¥X¦Psheets(1)ªº­È(¦¹®ÉxÀq»{¬°0,´Nºâ§Ú¥ý³]x=2¤]¤@¼Ë)¡F
'¨Ï¥Îsheets(x)®É¡A¦pªG¦³¨Æ¥ý³]¸mx=2,´N·|¶]¥Xsheets(2)ªº­È¡A©Ò¥H§Ú´N¥Î³o­Ó¡C ...
³Q½×¤å¹GºÆªº¤H µoªí©ó 2016-5-13 12:04


¥H¤U±Ô­z¤¤¬Ò°²³] x = 2
Sheets(x + 1)=Sheets(3) <> Sheets("3")
«e­± Sheets(3) ªº 3 ¬O Index ½s¸¹ (µ¥¦P¦b VBAProject ±M®×Á`ºÞ ¦Cªí¤¤ Sheet1(S1) «e­±¨º­Ó Sheet1<CodeName> ªº 1 )
«á­± Sheets("3") ªº 3 ¬O Sheet ªº ¦WºÙ(µ¥¦P¦b VBAProject ±M®×Á`ºÞ ¦Cªí¤¤ Sheet1(S1) «á­±¬A¸¹¤¤¶¡ªº S1<SheetName> )
°£«D§A±q«Ø¥ßÀɮ׶}©l«á´N¤@ª½·Ó¶¶§Ç²£¥Í Sheets("1"),Sheets("2")...
¥B¨S°µ¥ô¦ó¤u§@ªíªº¼W§R,²¾°Ê©Î§ó¦W,
§_«h Sheets(100) «ÜÃø ¯Á¤Þ¨ì Sheets("100").

§Aªº±¡§Î¤¤­Y Sheets(x + 1 & "") ¤£¯à¥¿½T¯Á¤Þ¨ì Sheets("3") ªº¸Ü,
§ï¦¨ Sheets((x + 1) & "") ¸Õ¸Õ. («á­±¥[¤W & "" ¬O±j¨î±N X+1 ªºµ²ªG§ï¦¨¦r¦ê, µ¥¦P "3")

¥H¤W¬O§Úªº²z¸Ñ,­Y¦³¿ù»~Åwªï«ü¥¿.

TOP

¦^´_ 4# luhpro

¶â §Ú¸Õ¤F§A´£¨ÑªºSheets((x + 1) & "")¡A x­È²×©ó·|¸òµÛ©Ò³]©wªº­È¶]¤F!!
§Ú¤§«eªº§@ªk¨ä¹ê¬O
1 ¨Æ¥ý¦b³Ì«e­±¶}¤@±i¤u§@ªí¡A
2 ±N·|¨Ï¥Î¨ìªº¤u§@ªíªº¦WºÙ¦C¦bAÄæ
3 µM«á¥Îºô¸ô¤W§ä¨ìªºµ{¦¡½X
  1. Sub AddSheet()
  2.    Dim i As Integer
  3.     For i = 1 To 76
  4.         Sheets.Add after:=Sheets(Sheets.Count)
  5.         Sheets(Sheets.Count).Name = Sheets(1).Range("A" & i)
  6.     Next
  7. End Sub
½Æ»s¥N½X
¶]¥X©Ò¦³ªº§Ú¹w©w­n±o¨ìªº¤u§@ªí¤§«á¡A
4 §R±¼§t¦³¤u§@ªí¦WºÙªº¤u§@ªí¡A
5 ¦A¶i¦æ¿z¿ï
PS ´N¬O»Ý­n¤À¨â­Ó¨BÆJ¡A¤£¹Laddsheet()©Ò»Ýªº®É¶¡µu¤£¤Ó¼vÅT¾ãÅé´N¬O¤F

·PÁÂÀ°§U!!!

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD