­ã´£³¡ªL

Aªí¬J¬O¦@¥Îªº, ¼W­ÓAAAªí¨ú¤½¦¡¤Î­È, ª½±µ¶K¤W§Y¥i,
À³¤£¶·¨C­ÓÀɮ׳£¥[¸ü¤@¦¸¤½¦¡§a!

¤Þ¥ÎA¤u§@ªíªº¤½¦¡­È_01.rar (216.57 KB)

¤U¸ü¦¸¼Æ: 7

2021-1-10 12:22

­ã´£³¡ªL

¦^´_ 16# ziv976688


Sub DATAÀÉ_AAAªí_¤½¦¡()
Dim AAA_Fx$(4), xR As Range, N%, xArea
AAA_Fx(1) = "=SMALL(IF(INDEX(A!$B$2:$S$51,,ROW($A1))="""",A!$A$2:$A$51),COLUMN(A$1))"
AAA_Fx(2) = "=SMALL(IF(INDEX(A!$V$2:$AM$51,,ROW($A1))="""",A!$A$2:$A$51),COLUMN(A$1))"
AAA_Fx(3) = "=SMALL(IF(INDEX(A!$AP$2:$BG$9,,ROW($A1))="""",A!$A$2:$A$9),COLUMN(A$1))"
AAA_Fx(4) = "=SMALL(IF(INDEX(A!$BJ$2:$CA$9,,ROW($A1))="""",A!$A$2:$A$9),COLUMN(A$1))"
For Each xR In Sheets("AAA").Range("B2,B52, B102, B152")
    N = N + 1
    xR.FormulaArray = AAA_Fx(N)
    xR.Copy xR(1, 2).Resize(1, 48)
    xR.Resize(1, 49).Copy xR(2, 1).Resize(17, 49)
    With xR.Resize(18, 49)
        .Value = .Value
        .Replace "#*", "", lookat:=xlWhole
    End With
Next
End Sub