jcchiang

回復 10# PJChen
儲存格公式中E3="",要寫成E3=""""

1) 無法執行
xRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("E3:E" & xRow).Formula = "=IF(E3="""",""無交貨"",T3&S3&R3)"
2)無法執行
Range("Z3:Z" & xRow).Formula = "=IF(AB3="""","""",AB3-Y3)"

3) 無法執行
Range("F2:F" & xRow).Formula = "=IF(MONTH(A3)<>MONTH(A4),SUM(INDIRECT(ADDRESS(MATCH(DATE(YEAR(A3),MONTH(A3),1),A:A,0),COLUMN(D3),3)):D3),"""")"

PJChen

回復 11# jcchiang

原來是出在雙引號,感謝,,,我再試試

准提部林

Sub 公式_01()
Dim xR As Range, xH As Range, xA As Range, C%, N&
Set xR = [C5] '第一區定位格
C = Application.Match("合計", Rows(4), 0) - xR.Column  '欄位數
Set xA = xR.Resize(7, C) '第一區(不含合計欄)
Application.ScreenUpdating = False
xA = "=IF(C4=0,"""",INT(C4/$C$3)&""箱""&TEXT(MOD(C4,$C$3),""+0;;;""))"
xA.Rows(1) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3=C4)*(飛比!$AP$4:$BH$70))"
xA.Rows(3) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=C4)*(飛比!$BJ$4:$CB$70))"
xA.Rows(5) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=C4)*(飛比!$CD$4:$CV$70))"
xA.Rows(6) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=C4)*(飛比!$CX$4:$DP$70))"
xR(1, C + 1).Resize(7) = "=IF($B5=""箱+瓶"","""",SUM(" & xA.Rows(1).Address(0, 0) & "))" '合計欄
'-----------------------------------------------
Set xA = xR.Resize(7, C + 1) '第一區(含合計欄)
Do
    N = N + 1: Set xH = xR(N * 9 + 1, 1)
    If xH(1, 0) <> "訂購數" Then Exit Do
    With xH.Resize(7, C + 1)
         xA.Copy .Cells
         .Value = .Value
    End With
Loop
xA.Value = xA.Value
End Sub

Xl0000048.rar (35.95 KB)

下載次數: 4

2020-3-20 13:14

======================================

PJChen

回復 13# 准提部林

感謝准大幫我起了一個頭,由於整個查帳內容還不完整,剩餘的資料,我再看看能否依樣畫葫蘆給補上~~謝謝