PJChen

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 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

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

2^#

發表於 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

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

3^#

發表於 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))

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

4^#

發表於 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

不知妳是要哪一種

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

5^#

發表於 2020-3-2 17:36 | 只看該作者

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

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

最新庫存-查帳.rar (83.12 KB)

VBA中的函數式取代.rar (103.59 KB)

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

6^#

發表於 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

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

7^#

發表於 2020-3-2 18:33 | 只看該作者

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

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

查帳_訂購數 = "=" & "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 '看幾筆資料
.Range("C5").Resize(1, xcol).Value = 查帳_訂購數
.Range("C5").Resize(1, xcol).Value = .Range("C5").Resize(1, xcol).Value
.Range("C14").Resize(xcol, xcol).Value = 查帳_訂購數
.Range("C14").Resize(xcol, xcol).Value = .Range("C14").Resize(xcol, xcol).Value
.Range("C6").Resize(1, xcol).Value = 查帳_訂購箱瓶
.Range("C6").Resize(1, xcol).Value = .Range("C6").Resize(1, xcol).Value
.Range("C15").Resize(xcol, xcol).Value = 查帳_訂購箱瓶
.Range("C15").Resize(xcol, xcol).Value = .Range("C15").Resize(xcol, xcol).Value
.Range("C7").Resize(1, xcol).Value = 查帳_廠缺
.Range("C7").Resize(1, xcol).Value = .Range("C7").Resize(1, xcol).Value
.Range("C16").Resize(xcol, xcol).Value = 查帳_廠缺
.Range("C16").Resize(xcol, xcol).Value = .Range("C16").Resize(xcol, xcol).Value
.Range("C8").Resize(1, xcol).Value = 查帳_廠缺箱瓶
.Range("C8").Resize(1, xcol).Value = .Range("C8").Resize(1, xcol).Value
.Range("C17").Resize(xcol, xcol).Value = 查帳_廠缺箱瓶
.Range("C17").Resize(xcol, xcol).Value = .Range("C17").Resize(xcol, xcol).Value
.Range("C9").Resize(xcol, xcol).Value = 查帳_劃單
.Range("C9").Resize(xcol, xcol).Value = .Range("C9").Resize(xcol, xcol).Value
.Range("C10").Resize(xcol, xcol).Value = 查帳_實出數
.Range("C10").Resize(xcol, xcol).Value = .Range("C10").Resize(xcol, xcol).Value
.Range("C19").Resize(xcol, xcol).Value = 查帳_實出數
.Range("C19").Resize(xcol, xcol).Value = .Range("C19").Resize(xcol, xcol).Value
.Range("C11").Resize(xcol, xcol).Value = 查帳_實出箱瓶
.Range("C11").Resize(xcol, xcol).Value = .Range("C11").Resize(xcol, xcol).Value
.Range("C20").Resize(xcol, xcol).Value = 查帳_實出箱瓶
.Range("C20").Resize(xcol, xcol).Value = .Range("C20").Resize(xcol, xcol).Value

複製代碼

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

8^#

發表於 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

TOP

jcchiang

中學生 Rank: 2

帖子: 234
主題: 19
精華: 0
積分: 276
點名: 0
作業系統: Windows XP
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2013-1-7
最後登錄: 2021-10-7

9^#

發表於 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

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

10^#

發表於 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),"")"

TOP