Board logo

標題: [發問] VBA中的函數式 取代 [打印本頁]
作者: PJChen    時間: 2020-3-1 17:59     標題: VBA中的函數式 取代

本帖最後由 PJChen 於 2020-3-1 18:00 編輯

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    時間: 2020-3-2 13:00

回復 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    時間: 2020-3-2 16:16

回復 2# jcchiang

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

回復 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    時間: 2020-3-2 17:36

本帖最後由 PJChen 於 2020-3-2 17:41 編輯

回復 4# jcchiang
我二種都要用,以目前訂購數公式來說,從C5:J5 / C14:J4
VBA中的函數式 取代.rar (103.59 KB) 是錯的,它不讓我刪除,請勿下載
[attach]31766[/attach]
作者: PJChen    時間: 2020-3-2 17:50

回復 4# jcchiang
我知道了,要這樣改...感謝您
.Range("C5").Resize(xcol, xcol).Value = 查帳_訂購數
.Range("C5").Resize(xcol, xcol).Value = .Range("C5").Resize(xcol, xcol).Value
作者: PJChen    時間: 2020-3-2 18:33

本帖最後由 PJChen 於 2020-3-2 19:00 編輯

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

  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    時間: 2020-3-3 11:31

回復 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    時間: 2020-3-3 16:36

回復 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    時間: 2020-3-7 23:31

回復 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),"")"
作者: jcchiang    時間: 2020-3-9 08:54

回復 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    時間: 2020-3-9 21:46

回復 11# jcchiang

原來是出在雙引號,感謝,,,我再試試
作者: 准提部林    時間: 2020-3-20 13:14

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

[attach]31802[/attach]

======================================
作者: PJChen    時間: 2020-3-20 22:24

回復 13# 准提部林

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



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)