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

[µo°Ý] ¤½¦¡µ{¦¡½X¤Æ

¦^´_ 44# ziv976688

SP = (xV.Offset(, -9) - SP) Mod 49: If SP = 0 Then SP = 49  'M2-V2 mod 49

A¦¡ : ·í -SP¤§«áªº®t­È=­t¼Æ®É¡A«hSP¦Û°ÊÂର¥¿¼Æ(§Y¦p=µ´¹ï­È)
SP = ABS(xV.Offset(, -9) - SP) Mod 49: If SP = 0 Then SP = 49  'M2-V2 mod 49


B¦¡ : ·í-SP¤§«áªº®t­È=­t¼Æ®É¡A«hSP¦Û°Ê¦A+49Âର¥¿¼Æ(§Y¦p=¤@¯ëªº¨ç¼Æ­pºâ)
SP = (xV.Offset(, -9) - SP + 49) Mod 49: If SP = 0 Then SP = 49  'M2-V2 mod 49
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 48# ziv976688


B¦¡ : ·í-SP¤§«áªº®t­È=­t¼Æ®É¡A«hSP¦Û°Ê¦A+49Âର¥¿¼Æ(§Y¦p=¤@¯ëªº¨ç¼Æ­pºâ)
SP = (xV.Offset(, -9) - SP + 49) Mod 49: If SP = 0 Then SP = 49  'M2-V2 mod 49

­Y¬O
11-90 = -79¡A¦A+49Åܦ¨ -30 ¡AÁÙ¬O­t¼Æ«ç»ò¿ì?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 50# ziv976688

¤F¸Ñ¡A¼Æ¦r­Y¬O0~48´N¨S¦³°ÝÃD
¦]¬°¤§«e¼g¤½¦¡¦³°Ý¹L§A¡A¼Æ¦r¬O§_±±¨î¦b99¥H¤U¡A©Ò¥H¦³ÂI»~·|¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2021-7-29 13:54 ½s¿è

¦^´_ 55# ziv976688

¯u¤£¦n·N«ä¡A¤pBUG¤£Â_¡C
Sub ¾l¼Æ¦U¨ú1()
    Dim xD As Object, xS As Worksheet, xR As Range, SP, N
    Set xD = CreateObject("Scripting.Dictionary")
   
    For Each xS In Sheets(Split("·Ç2¶i3 ·Ç3¶i4 ·Ç4¶i5 ·Ç5¶i6 ·Ç6¶i7 ·Ç7¶i8"))    '¨úªí®æ
        For Each xR In xS.Range("D2:J" & xS.[B65536].End(xlUp).Row)    '¨úÀx¦s®æ
            For Each SP In Split(xR, ",")    '¤À¶}¼Æ¦r
                If Val(SP) > 0 Then xD(Val(SP)) = ""    '¦r¨å²Õ¦X
            Next
        Next
        N = xD.Count
        xS.[A2:A110].ClearContents    '²M°£Àx¦s®æ¤º®e
        xS.[A2] = IIf(N = 0, "", N & "­Ó")
        xS.[A3] = "¸¹½X"
       If N > 0 Then
            With xS.[A4].Resize(N)
                .Value = Application.Transpose(xD.keys)
                'N > 1®É¤~»Ý­n±Æ§Ç¡A¤£µM·|¿ù
                If N > 1 Then .Sort key1:=.Item(1), Order1:=xlAscending, Header:=xlNo
            End With
        End If
        xD.RemoveAll
    Next
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD