[發問] VBA中的函數式 取代

PJChen

Dear,
在工作表中可以自定義函數使公式變得簡潔,但因為用得太多了,當需要修改時,也是很令人混淆...
想改在寫程式時,把公式用一個代碼xc表示,最後變成值,這樣就不用一直把冗長的函數式寫出來,但是"行不通",
請問是否有其他的寫法or需要什麼物件,試了又試總是不行..
                i = "C1:J1"
                    xcol = Range(i).Columns.Count
                    Set xc = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=查帳!$B$3)*(飛比!$AP$3:$BH$3=查帳!C4)*(比菲多!$AP$4:$BH$70))" '查帳_訂購數
                        .Range("C5").Resize(1, xcol).Value = "=" & xc
                        .Range("C5").Resize(1, xcol).Value = .Range("C5").Resize(1, xcol).Value

jcchiang

回復 1# PJChen

改這樣試試
xc = "=SUMPRODUCT((飛比!$F$4:$F$70=查帳!$B$3)*(飛比!$AP$3:$BH$3=查帳!C4)*(比菲多!$AP$4:$BH$70))" '查帳_訂購數
                        .Range("C5").Resize(1, xcol).Value = xc

PJChen

回復 2# jcchiang

改這樣 在C5儲存格 資料正確,但其他儲存格得出的值會錯誤,原因為在於紅字部份,C4不是$C$4 應能隨著儲存格不同而位移,可是將公式用xc取代時,卻變得不會移動,只是一個死的東西,請問能有其他寫法,可以讓公式活用嗎?
SUMPRODUCT((飛比!$F$4:$F$70=查帳!$B$3)*(飛比!$AP$3:$BH$3=查帳!C4)*(比菲多!$AP$4:$BH$70))

jcchiang

回復 3# PJChen

xc = "=SUMPRODUCT((飛比!$F$4:$F$70=查帳!$B$3)*(飛比!$AP$3:$BH$3=查帳!C4)*(比菲多!$AP$4:$BH$70))" '查帳_訂購數
Range("C5").Resize(1, xcol).Value = xc
如果xcol=5會由C5至G5放入公式C4,D4,E4,F4,G4
Range("C5").Resize( xcol,1).Value = xc
如果xcol=5會由C5至C9放入公式C4,C5,C6,C7,C8

不知妳是要哪一種

PJChen

回復 4# jcchiang
我二種都要用,以目前訂購數公式來說,從C5:J5 / C14:J4
VBA中的函數式 取代.rar (103.59 KB) 是錯的,它不讓我刪除,請勿下載
最新庫存-查帳.rar (83.12 KB)

下載次數: 7

2020-3-2 17:40

PJChen

回復 4# jcchiang
我知道了,要這樣改...感謝您
.Range("C5").Resize(xcol, xcol).Value = 查帳_訂購數
.Range("C5").Resize(xcol, xcol).Value = .Range("C5").Resize(xcol, xcol).Value

PJChen

回復 6# PJChen
這樣做,資料還是錯吔...除了C5一整列的資料正確,其餘連執行都有問題,卡住了...

1. 查帳_訂購數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3=C4)*(飛比!$AP$4:$BH$70))"
   
2. 查帳_訂購箱瓶 = "=" & "IF(C5=0,"",INT(C5/$C$3)&IF(MOD(C5,$C$3)=0,""箱"",""箱+""&MOD(C5,$C$3)))"
   
3. 查帳_廠缺 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=C4)*(飛比!$BJ$4:$CB$70))"
   
4. 查帳_廠缺箱瓶 = "=" & "IF(C7=0,"",INT(C7/$C$3)&""箱"")&IF(MOD(C7,$C$3)=0,"",""+""&MOD(C7,$C$3))"
   
5. 查帳_劃單 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=C4)*(飛比!$CD$4:$CV$70))"
   
6. 查帳_實出數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=C4)*(飛比!$CX$4:$DP$70))"
   
7. 查帳_實出箱瓶 = "=" & "IF(C10=0,"",INT(C10/$C$3)&""箱"")&IF(MOD(C10,$C$3)=0,"",""+""&MOD(C10,$C$3))"
   
8. 
   
9.                     i = "C1:J1"
   
10.                     xcol = Range(i).Columns.Count  '看幾筆資料
    
11.                         .Range("C5").Resize(1, xcol).Value = 查帳_訂購數
    
12.                         .Range("C5").Resize(1, xcol).Value = .Range("C5").Resize(1, xcol).Value
    
13.                             .Range("C14").Resize(xcol, xcol).Value = 查帳_訂購數
    
14.                             .Range("C14").Resize(xcol, xcol).Value = .Range("C14").Resize(xcol, xcol).Value
    
15.                            
    
16.                                 .Range("C6").Resize(1, xcol).Value = 查帳_訂購箱瓶
    
17.                                 .Range("C6").Resize(1, xcol).Value = .Range("C6").Resize(1, xcol).Value
    
18.                                     .Range("C15").Resize(xcol, xcol).Value = 查帳_訂購箱瓶
    
19.                                     .Range("C15").Resize(xcol, xcol).Value = .Range("C15").Resize(xcol, xcol).Value
    
20.                                     
    
21.                                         .Range("C7").Resize(1, xcol).Value = 查帳_廠缺
    
22.                                         .Range("C7").Resize(1, xcol).Value = .Range("C7").Resize(1, xcol).Value
    
23.                                             .Range("C16").Resize(xcol, xcol).Value = 查帳_廠缺
    
24.                                             .Range("C16").Resize(xcol, xcol).Value = .Range("C16").Resize(xcol, xcol).Value
    
25.                                             
    
26.                                                 .Range("C8").Resize(1, xcol).Value = 查帳_廠缺箱瓶
    
27.                                                 .Range("C8").Resize(1, xcol).Value = .Range("C8").Resize(1, xcol).Value
    
28.                                             .Range("C17").Resize(xcol, xcol).Value = 查帳_廠缺箱瓶
    
29.                                             .Range("C17").Resize(xcol, xcol).Value = .Range("C17").Resize(xcol, xcol).Value
    
30.                                         .Range("C9").Resize(xcol, xcol).Value = 查帳_劃單
    
31.                                         .Range("C9").Resize(xcol, xcol).Value = .Range("C9").Resize(xcol, xcol).Value
    
32.                                     .Range("C10").Resize(xcol, xcol).Value = 查帳_實出數
    
33.                                     .Range("C10").Resize(xcol, xcol).Value = .Range("C10").Resize(xcol, xcol).Value
    
34.                                 .Range("C19").Resize(xcol, xcol).Value = 查帳_實出數
    
35.                                 .Range("C19").Resize(xcol, xcol).Value = .Range("C19").Resize(xcol, xcol).Value
    
36.                             .Range("C11").Resize(xcol, xcol).Value = 查帳_實出箱瓶
    
37.                             .Range("C11").Resize(xcol, xcol).Value = .Range("C11").Resize(xcol, xcol).Value
    
38.                         .Range("C20").Resize(xcol, xcol).Value = 查帳_實出箱瓶
    
39.                         .Range("C20").Resize(xcol, xcol).Value = .Range("C20").Resize(xcol, xcol).Value

複製代碼

jcchiang

回復 7# PJChen
2種寫法提供參考
因為妳的公式太多樣,先依各項欄位填寫,如有可以合併請自行調整
箱+瓶修改為箱+瓶(1)/箱+瓶(2)/箱+瓶(3),以便判斷(Excel內容請修正)
箱+瓶(1)--訂購數
箱+瓶(2)--廠缺
箱+瓶(3)--實出數
轉為數值一併於程式後轉換(逐項轉換程式保留,需要自行開啟)
功力沒很好,是否還能簡化,就請其他高手幫忙了

Sub ex1()
'查帳_訂購數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3=C4)*(飛比!$AP$4:$BH$70))"
'查帳_訂購箱瓶 = "=" & "IF(C5=0,"",INT(C5/$C$3)&IF(MOD(C5,$C$3)=0,""箱"",""箱+""&MOD(C5,$C$3)))"
'查帳_廠缺 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=C4)*(飛比!$BJ$4:$CB$70))"
'查帳_廠缺箱瓶 = "=" & "IF(C7=0,"",INT(C7/$C$3)&""箱"")&IF(MOD(C7,$C$3)=0,"",""+""&MOD(C7,$C$3))"'
'查帳_劃單 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=C4)*(飛比!$CD$4:$CV$70))"
'查帳_實出數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=C4)*(飛比!$CX$4:$DP$70))"
'查帳_實出箱瓶 = "=" & "IF(C10=0,"",INT(C10/$C$3)&""箱"")&IF(MOD(C10,$C$3)=0,"",""+""&MOD(C10,$C$3))"
i = "C1:J1"
xcol = Range(i).Columns.Count  '看幾筆資料
For Each x In Range([b1], [b65535].End(3)) 'Range("B1:b20")
   If x = "訂購數" Then
      查帳_訂購數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3= " & _
                    x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$AP$4:$BH$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購數
   '  Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "箱+瓶(1)" Then
      查帳_訂購箱瓶 = "=" & "IF(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "=0,"""",INT(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "/$C$3)&IF(MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      ",$C$3)=0,""箱"",""箱+""&MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3)))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購箱瓶
   '  Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "廠缺" Then
      查帳_廠缺 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=" & _
                  x.Offset(-3, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$BJ$4:$CB$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_廠缺
  '   Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "箱+瓶(2)" Then
      查帳_廠缺箱瓶 = "=" & "IF(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "=0,"""",INT(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "/$C$3)&""箱"")&IF(MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      ",$C$3)=0,"""",""+""&MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_廠缺箱瓶
   '  Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "劃單" Then
      查帳_劃單 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=" & _
                   x.Offset(-5, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$CD$4:$CV$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_劃單
'    Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "實出數" Then
      查帳_實出數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=" & _
                    x.Offset(-6, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$CX$4:$DP$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_實出數
'    Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   ElseIf x = "箱+瓶(3)" Then
      查帳_實出箱瓶 = "=" & "IF(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                      "=0,"""",INT(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                     "/$C$3)&""箱"")&IF(MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                     ",$C$3)=0,"""",""+""&MOD(" & x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                     ",$C$3))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_實出箱瓶
  '   Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = Range(x.Address).Offset(0, 1).Resize(1, xcol).Value
   End If
Next
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value '一併轉為數值
End Sub

----------------------------------------------------------------------------------------------------------------------------------------------
Sub ex2()
i = "C1:J1"
xcol = Range(i).Columns.Count  '看幾筆資料
x = 1
Do While x < Range("B65535").End(3).Row + 1
   Select Case Cells(x, "B")
      Case Is = "訂購數"
         查帳_訂購數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3= " & _
                      Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$AP$4:$BH$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購數
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "箱+瓶(1)"
         查帳_訂購箱瓶 = "=" & "IF(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "=0,"""",INT(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "/$C$3)&IF(MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         ",$C$3)=0,""箱"",""箱+""&MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3)))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購箱瓶
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "廠缺"
         查帳_廠缺 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=" & _
                     Cells(x, "B").Offset(-3, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$BJ$4:$CB$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_廠缺
   '     Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "箱+瓶(2)"
         查帳_廠缺箱瓶 = "=" & "IF(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "=0,"""",INT(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         "/$C$3)&""箱"")&IF(MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                         ",$C$3)=0,"""",""+""&MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ",$C$3))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_廠缺箱瓶
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "劃單"
         查帳_劃單 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=" & _
                     Cells(x, "B").Offset(-5, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$CD$4:$CV$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_劃單
    '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "實出數"
        查帳_實出數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=" & _
                     Cells(x, "B").Offset(-6, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & ")*(飛比!$CX$4:$DP$70))"
        Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_實出數
   '    Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
      Case Is = "箱+瓶(3)"
        查帳_實出箱瓶 = "=" & "IF(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        "=0,"""",INT(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        "/$C$3)&""箱"")&IF(MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        ",$C$3)=0,"""",""+""&MOD(" & Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False) & _
                        ",$C$3))"
        Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_實出箱瓶
    '   Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value
   End Select
   x = x + 1
Loop
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value '一併轉為數值

End Sub

jcchiang

回復 7# PJChen
濃縮一下,2種寫法提供參考
查帳_訂購箱瓶 = "=" & "IF(C5=0,"",INT(C5/$C$3)&IF(MOD(C5,$C$3)=0,""箱"",""箱+""&MOD(C5,$C$3)))"
查帳_廠缺箱瓶 = "=" & "IF(C7=0,"",INT(C7/$C$3)&""箱"")&IF(MOD(C7,$C$3)=0,"",""+""&MOD(C7,$C$3))"'
查帳_實出箱瓶 = "=" & "IF(C10=0,"",INT(C10/$C$3)&""箱"")&IF(MOD(C10,$C$3)=0,"",""+""&MOD(C10,$C$3))"
這三個公式應該一樣,只是計算位置不同,統一使用"查帳_訂購箱瓶"的公式,計算位置會更動
所以EXCEL內維持用"箱+瓶"判斷

Sub ex1()
i = "C1:J1"
xcol = Range(i).Columns.Count  '看幾筆資料
For Each x In Range([b2], [b65535].End(3)) 'Range("B1:b20")
r = x.Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False)
   If x = "訂購數" Then
      查帳_訂購數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3= " & r & ")*(飛比!$AP$4:$BH$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購數
   ElseIf x = "箱+瓶" Then
      查帳_訂購箱瓶 = "=" & "IF(" & r & "=0,"""",INT(" & r & "/$C$3)&IF(MOD(" & r & ",$C$3)=0,""箱"",""箱+""&MOD(" & r & ",$C$3)))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購箱瓶
   ElseIf x = "廠缺" Then
      r1 = Range(r).Offset(-2, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
      查帳_廠缺 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=" & r1 & ")*(飛比!$BJ$4:$CB$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_廠缺
   ElseIf x = "劃單" Then
      r1 = Range(r).Offset(-4, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
      查帳_劃單 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=" & r1 & ")*(飛比!$CD$4:$CV$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_劃單
   ElseIf x = "實出數" Then
      r1 = Range(r).Offset(-5, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
      查帳_實出數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=" & r1 & ")*(飛比!$CX$4:$DP$70))"
      Range(x.Address).Offset(0, 1).Resize(1, xcol).Value = 查帳_實出數
   End If
Next
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value '轉為數值
End Sub
-----------------------------------------------------------------------------------------------
Sub ex2()
i = "C1:J1"
xcol = Range(i).Columns.Count  '看幾筆資料
x = 2
Do While x < Range("B65535").End(3).Row + 1
   r = Cells(x, "B").Offset(-1, 1).Address(RowAbsolute:=False, columnAbsolute:=False)
   Select Case Cells(x, "B")
      Case Is = "訂購數"
         查帳_訂購數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3= " & r & ")*(飛比!$AP$4:$BH$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購數
      Case Is = "箱+瓶"
         查帳_訂購箱瓶 = "=" & "IF(" & r & "=0,"""",INT(" & r & "/$C$3)&IF(MOD(" & r & ",$C$3)=0,""箱"",""箱+""&MOD(" & r & ",$C$3)))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_訂購箱瓶
      Case Is = "廠缺"
         r1 = Range(r).Offset(-2, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
         查帳_廠缺 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=" & r1 & ")*(飛比!$BJ$4:$CB$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_廠缺
      Case Is = "劃單"
         r1 = Range(r).Offset(-4, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
         查帳_劃單 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=" & r1 & ")*(飛比!$CD$4:$CV$70))"
         Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_劃單
      Case Is = "實出數"
         r1 = Range(r).Offset(-5, 0).Address(RowAbsolute:=False, columnAbsolute:=False)
        查帳_實出數 = "=" & "SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=" & r1 & ")*(飛比!$CX$4:$DP$70))"
        Cells(x, "B").Offset(0, 1).Resize(1, xcol).Value = 查帳_實出數
   End Select
   x = x + 1
Loop
Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value = Range("C1").Resize(Range("C65535").End(3).Row, xcol).Value '轉為數值
End Sub

PJChen

回復 9# jcchiang

本想改善表格,讓它簡潔又RUN快些,好像腦袋變得更想不清楚了...
先請教另個公式問題,快把我搞瘋了....
以下3個公式,直接key在儲存格時都沒問題,但為什麼在VBA中,完全無法執行?
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),"")"