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

¥i¥HÀ°§Ú¼g°j°é¨Ã­×¥¿¿ù»~¶Ü

¥i¥HÀ°§Ú¼g°j°é¨Ã­×¥¿¿ù»~¶Ü

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-1-19 10:17 ½s¿è

Range("K2") 1-140µ§¸ê®Æ(¥i¥H¯B°Ê¶Ü)
Range("K1") 1--7µ§¸ê®Æ(¥i¥H¯B°Ê¶Ü)
Sub §¹¦¨¶]ªÀ()
'
' §¹¦¨¶]ªÀ ¥¨¶°
''
  Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1"
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "7"
        °õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "6"
        °õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "5"
        °õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "4"
°õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "3"
°õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "2"
°õ¦æSUB §ÓÄ@½T©w
Range("K1").Select
      ActiveCell.FormulaR1C1 = "1"
      °õ¦æSUB §ÓÄ@½T©w
Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "2"
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "7"
        °õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "6"
        °õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "5"
        °õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "4"
°õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "3"
°õ¦æSUB §ÓÄ@½T©w
      Range("K1").Select
      ActiveCell.FormulaR1C1 = "2"
°õ¦æSUB §ÓÄ@½T©w
Range("K1").Select
      ActiveCell.FormulaR1C1 = "1"
      °õ¦æSUB §ÓÄ@½T©w

End Sub

¦^´_ 1# opman

§Ú¦Û¤vºN¯Á¼g¦¨³o¼Ë¦ý«ù¦æ¦n¤[¡A¦³¿ìªkÁYµu®É¶¡¶Ü
    Sub §¹¦¨¶]ªÀ()
'
' §¹¦¨¶]ªÀ ¥¨¶°
'
Dim s As Integer
For s = 1 To 128
    Range("K2") = s
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "7"
        Columns("F:F").Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="N", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "6"
        Columns("F:F").Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="N", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "5"
        Columns("F:F").Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="N", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "4"
        Columns("F:F").Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="N", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "3"
        Columns("F:F").Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="N", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "2"
        Columns("F:F").Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="N", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "1"
            Columns("F:F").Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="N", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
  Next s
End Sub

TOP

¦^´_ 1# opman

·Ó­ìPO¤å¬O¥i¥HÁY´î¦p¤U

Sub EX()
    For Each xK2 In Array("1", "2")
        Range("K2") = xK2
        For Each xK1 In Array("7", "6", "5", "4", "3", "2", "1")
            Range("K1") = xK1
            °õ¦æSUB §ÓÄ@½T©w
        Next
    Next
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# opman


¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub §¹¦¨¶]ªÀ()
  3. '
  4. ' §¹¦¨¶]ªÀ ¥¨¶°
  5. '
  6. Dim s As Integer, i As Integer
  7. Application.ScreenUpdating = False
  8. For s = 1 To 128
  9.     Range("K2") = s
  10.     For i = 7 To 1 Step -1
  11.         Range("K1") = i
  12.         Columns("F:F").Copy
  13.         With Range("H1").EntireColumn
  14.             .Cells(1).PasteSpecial Paste:=xlPasteValues
  15.             .Replace What:="N", Replacement:="", LookAt:=xlPart
  16.             .Copy
  17.         End With
  18.         Range("C1").PasteSpecial Paste:=xlPasteValues
  19.     Next
  20. Next
  21. Application.CutCopyMode = False
  22. Application.ScreenUpdating = True
  23. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD