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

[µo°Ý] ®Ö¨ú¤è¶ô ¦^¦s¸ê®Æ PART~2

¥H¥¨¶°ªº¡½TRUE,¨M©w¸ê®Æ¦h¹è

½Ð±Ð¦U¦ì¤j®v­Ì,¤È¦w

¯à§_¥H¥¨¶°ªº¥\¯à¹F¨ì¦¹®ÄªG,   
1.­º¥ý¦bSheet1 ©³¤U¿é¤J¸ê®Æ«á«ö¤U¡½¥[¤u¤@ B3:TRUE ¡B¡½¥[¤u¤@ B5:TRUE
2.SHEET2 §e²{µ²ªG¦p¤U

kai~19.rar (100.73 KB)


TOP

¦^´_ 1# kai6929
ªþÀɦ³¤T­Ó ¤@¯ë¼Ò²Õ³£¦³ check() ³oµ{¦¡·|·d½k¶îªº
  1. Option Explicit
  2. Sub check()
  3.     Dim K As String, M As Boolean, xRow As Integer, xi As Integer
  4.     With ActiveSheet.Shapes(Application.Caller)
  5.         With .TextFrame
  6.             K = .Characters.Text
  7.             If Left(K, 1) = "¡½" Then
  8.                 .Characters.Text = "¡¼¥[¤u¤@"
  9.                 M = False
  10.             Else
  11.                 .Characters.Text = "¡½¥[¤u¤@"
  12.                 M = True
  13.             End If
  14.             .Characters(1, Len(K) + 1).Font.Size = 10
  15.             .Characters(1, 1).Font.Size = 10
  16.         End With
  17.         .TopLeftCell.Offset(, 1) = M
  18.         .TopLeftCell.Offset(, 2) = IIf(CSng(M) = 0, 0, 1)
  19.     End With
  20.     Sheet2.UsedRange.Offset(1).Clear
  21.     xRow = 3
  22.     With ActiveSheet
  23.     Do While .Cells(xRow, "C") <> ""
  24.         If .Cells(xRow, "C") = 1 Then
  25.             xi = xi + 1
  26.             Sheet2.Rows(1).Copy Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1)
  27.            With Sheet2.Cells(Rows.Count, "A").End(xlUp)
  28.            .Cells(1) = xi
  29.            .Cells(1, 3) = ActiveSheet.Cells(xRow, "D")
  30.            .Cells(1, 6) = ActiveSheet.Cells(xRow, "H")
  31.            .Cells(1, 7) = ActiveSheet.Cells(xRow, "I")
  32.            .Cells(1, 9) = ActiveSheet.Cells(xRow, "K")
  33.            End With
  34.         End If
  35.         xRow = xRow + 1
  36.     Loop
  37.     End With
  38. End Sub
½Æ»s¥N½X

TOP

·PÁ¤j®v±zªº¦^ÂÐ,³o¥¿¬O§Ú­nªº

TOP

[µo°Ý] ®Ö¨ú¤è¶ô ¦^¦s¸ê®Æ PART~2

¤j®v¦n
           ¦¹¥¨¶°¬°¬Y¤j®vÀ°¦£§¹¦¨¤§³Ç§@,·Q¦b§ï¦¨¹F¨ì¥H¤Uªº¥\¯à¬O§_¥iÀ°À°¦£,  
1.­º¥ý¦bSheet1 ©³¤U¿é¤J¸ê®Æ«á«ö¤U
¡½¥[¤u¤@  C25:= 1    ¡½¥[¤u¤@  C26:= 1    ¡½¥[¤u¤@  C27:= 1   
2.SHEET2 §e²{µ²ªG¦p¤U   ¶µ¦¸ L 13¥H¤U ¦Û°Ê²£¥Í
3.¦bSheet1 ©³«ö¤U
¡¼¥[¤u¤@  C25:= 0    ¡¼¥[¤u¤@  C26:= 0    ¡¼¥[¤u¤@  C27:= 0  
4.SHEET2 §e²{µ²ªG¦p¤U   ¶µ¦¸ L13:S27¥H¤U ²M°£¤º®e


Option Explicit
Sub check()
    Dim K As String, M As Boolean, xRow As Integer, xi As Integer
    With ActiveSheet.Shapes(Application.Caller)
        With .TextFrame
            K = .Characters.Text
            If Left(K, 1) = "¡½" Then
                .Characters.Text = "¡¼¥[¤u¤@"
                M = False
            Else
                .Characters.Text = "¡½¥[¤u¤@"
                M = True
            End If
            .Characters(1, Len(K) + 1).Font.Size = 10
            .Characters(1, 1).Font.Size = 10
        End With
        .TopLeftCell.Offset(, 1) = M
        .TopLeftCell.Offset(, 2) = IIf(CSng(M) = 0, 0, 1)
    End With
    Sheet2.UsedRange("L13:S27").ClearContents
    xRow = 25
    With ActiveSheet
    Do While .Cells(xRow, "C") <> ""
        If .Cells(xRow, "C") = 1 Then
            xi = xi + 1
            Sheet2.Rows(1).Copy Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1)
           With Sheet2.Cells(Rows.Count, "A").End(xlUp)
           .Cells(1) = xi
           .Cells(12, 14) = ActiveSheet.Cells(xRow, "F")
           .Cells(12, 17) = ActiveSheet.Cells(xRow, "H")
           .Cells(12, 18) = ActiveSheet.Cells(xRow, "J")
           .Cells(12, 19) = ActiveSheet.Cells(xRow, "K")
           End With
        End If
        xRow = xRow + 1
    Loop
    End With
End Sub
kai~20.rar (72.82 KB)
1

µû¤À¤H¼Æ

    • GBKEE: ­«½Æµo©«ª÷¿ú -1

­Y¬Û¦PÃD¥Ø.. «Øij¥H­ì©«¦^´_µo°Ý
1

µû¤À¤H¼Æ

    • GBKEE: §Ú«ÜÃÙ¦Pª÷¿ú + 1

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-11-6 16:51 ½s¿è

¦^´_ 5# mark15jill
½T¹ê¬O¶·¦p¦¹ªº,¤@¨Ó¥iª¾°ÝÃDªº·½¬G ,¤G¬O¥i¸`¬Ù½×¾Â¸ê·½.

¦^´_ 4# kai6929
³o¥u¬O±N¥Ø­ì¥». 1#µo°Ý­n½Æ»s¦bSheet2.A2ªº¸ê®Æ,²¾°Ê¨ìSheet2.L13
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub check()
  3.     Dim K As String, M As Boolean, xRow As Integer, xi As Integer
  4.     Dim Rng As Range
  5.     With ActiveSheet.Shapes(Application.Caller)
  6.         With .TextFrame
  7.             K = .Characters.Text
  8.             If Left(K, 1) = "¡½" Then
  9.                 .Characters.Text = "¡¼¥[¤u¤@"
  10.                 M = False
  11.             Else
  12.                 .Characters.Text = "¡½¥[¤u¤@"
  13.                 M = True
  14.             End If
  15.             .Characters(1, Len(K) + 1).Font.Size = 10
  16.             .Characters(1, 1).Font.Size = 10
  17.         End With
  18.         .TopLeftCell.Offset(, 1) = M
  19.         .TopLeftCell.Offset(, 2) = IIf(CSng(M) = 0, 0, 1)
  20.     End With
  21.     Sheet2.Range("L13").CurrentRegion.Offset(1).Clear
  22.     Set Rng = Sheet2.Range("L13").CurrentRegion.Rows(1)
  23.     With ActiveSheet
  24.         xRow = 25
  25.         Do While .Cells(xRow, "C") <> ""
  26.             If .Cells(xRow, "C") = 1 Then
  27.                 xi = xi + 1
  28.                 Rng.Copy
  29.                 With Sheet2.Cells(Rows.Count, "L").End(xlUp).Offset(1)
  30.                     .PasteSpecial
  31.                     .Cells(1) = xi                               '¶µ¦¸
  32.                     '¸ê®Æ¦s©ñ¦b¦X¨ÖÀx¦s­Óªº²Ä1­ÓCells
  33.                     .Cells(1, 2) = ActiveSheet.Cells(xRow, "A")  '¥[¤u¶µ¥Ø
  34.                     .Cells(1, 3) = ActiveSheet.Cells(xRow, "F")  '¥[¤u³W®æ
  35.                     .Cells(1, 6) = ActiveSheet.Cells(xRow, "H")  '(³æ»ù)
  36.                     .Cells(1, 7) = ActiveSheet.Cells(xRow, "I")  '¦¸¼Æ
  37.                     .Cells(1, 8) = ActiveSheet.Cells(xRow, "K")  '¥[¤u¶O¥Î
  38.                 End With
  39.             End If
  40.             xRow = xRow + 1
  41.         Loop
  42.     End With
  43.     Application.CutCopyMode = False
  44. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# GBKEE

¨ä¹ê ¬O¦]¬°§Ú¤§«e¥Ç¹L..
³QªÈ¥¿ «¢«¢

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¥»©«³Ì«á¥Ñ kai6929 ©ó 2012-11-6 17:28 ½s¿è

¤j®v¯uªº¬O¼F®`ªº«Ü.
¤G¤T¤U´N¸Ñ¨M§Úªº°ÝÃD,¦ý¦³¤@¨Æ§Ú¤£©ú¥Õ¬°¦ó¦¹¥¨¶°©ñ¨ì§Úªº Sheet1 «K¤£¯à¨Ï¥Î.
¥i¥H«ü¾É¤@¤U¶Ü

TOP


©ñ¦b§Ú­ì¦³ªº  Sheet1 «K¤£¯à¨Ï¥Î¬°¤°»ò?

TOP

¦^´_ 8# kai6929
¨ºµ{¦¡½X¬Oµ¹Sheet2¥Îªº,¸ê®Æ¤S­n§ï©ñSheet1,¦³ÃöSheet2ªºµ{¦¡½X,¨º§A­n§ï¤@¤U

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD