返回列表 上一主題 發帖

[發問] 引用A工作表值的公式語法

A表既是共用的, 增個AAA表取公式及值, 直接貼上即可,
應不須每個檔案都加載一次公式吧!

引用A工作表的公式值_01.rar (216.57 KB)

TOP

回復 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

TOP

        靜思自在 : 手心向下是助人,手心向上是求人;助人快樂,求人痛苦。
返回列表 上一主題