帖子: 8
主題: 1
精華: 0
積分: 45
點名: 0
作業系統: XP
軟體版本: Office 2007
閱讀權限: 10
性別: 女
註冊時間: 2010-11-25
最後登錄: 2011-6-30

1^# 跳轉到 » 正序看帖

字體大小: tT

發表於 2010-11-26 12:19 | 只看該作者

[發問] 寫一個天數累加的自訂函數

大家好~~~在腦袋打結時，可以找到這個討論版，實在覺得太有幸了!!
欲麻煩各位先進們解救指點

想寫一個函數，用來計算天數的累加
功能大概是：
在B欄位有下拉選單，其值有PD, CL
若選單下拉選PD時，其C欄位會開始計算pending day (此位置也是欲放函數判斷的位置)
選單下拉選CL時，其C欄會停止累加，
但是下次選單再選PD時，C欄又需開始計算累加。

例如：
11/20 選PD時，C欄計值 1，過兩天11/22打開Excel，C欄的計值會自動成 3，並且也在這天將選單切換CL。
11/24再打開Excel，將選單改為PD，關閉檔案。
11/25打開，其C欄的計值會自動成 4。

目前這個功能，我分成很多地方來處理@@
煩請見附件

計算pending day.zip (10.22 KB) 演示，以下為我的作法簡單說明：
1. 在C 欄有IF函數判斷：B 欄為PD時，值為1
2. 在D 欄也有IF函數判斷：C 欄值為1時，帶入今天的日期
3. 寫VBA在Excel關閉時，判斷D, C 欄皆為true時，就copy D 欄值至E欄
4. 一樣寫VBA在Excel關閉時，會更新E 欄的日期為今天的日期
5. 寫VBA在在Excel打開時，判斷今天日期與E 欄的日期不同，則做相減計算動作，帶入值C 欄去

我覺得我目前的作法搞得太複雜多餘......
所以一直很想簡化成函數來跑，不知道是否可行!? How??
再麻煩各位先進們指教一番，感謝！

naraohya

小學生 Rank: 1

帖子: 8
主題: 1
精華: 0
積分: 45
點名: 0
作業系統: XP
軟體版本: Office 2007
閱讀權限: 10
性別: 女
註冊時間: 2010-11-25
最後登錄: 2011-6-30

9^#

發表於 2010-11-27 23:29 | 只看該作者

回復 7# Hsieh

謝謝Hsieh大的回覆，我先試了作法，明天有時間再來好好消化程式碼 XD (我對 offset的用法老是感到頭昏@@)
有發現一個小錯誤：
第二篇程式碼第8列 :A.Value="CL" 好像應該設"PD"

另外，只有該列B欄值為PD，其D欄才更新日期，但B欄值為CL的卻未更新，
這樣對於之後B欄的CL又改為PD時，其pending day算出值在邏輯上是不對的。

兩位大大的寫法，都有給我新啟發，期盼我可以碰撞出滿意的作法!! 有的話會再來分享唷~~希望!!

TOP

naraohya

小學生 Rank: 1

帖子: 8
主題: 1
精華: 0
積分: 45
點名: 0
作業系統: XP
軟體版本: Office 2007
閱讀權限: 10
性別: 女
註冊時間: 2010-11-25
最後登錄: 2011-6-30

8^#

發表於 2010-11-27 23:27 | 只看該作者

回復 6# GBKEE

謝謝GBKEE大不厭其煩的回覆，檔案有試出來
我放錯到工作簿事件去了.....(揍飛)
不過試出後，跟我要的運作是不太一樣，以下：
1. B欄一選PD時, 其C欄的pending day並沒有先累計值1，而是0
2. 呈上，例：在B3欄選值PD時，期望是僅旁邊的C3欄有值，而非整排的C欄都有值。
3. 隔天打開Excel，其pending day未自動更新其天數，是再重新點選B欄選擇PD，pending day才更新。

總之，非常謝謝，提供了我另一種VBA的學習寫法！

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 255
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-25

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

7^#

發表於 2010-11-27 18:32 | 只看該作者

回復 1# naraohya

不知道有沒有理解錯誤
直接讓D欄記住第一次B欄選PD時的日期(這用CHANGE事件來輸入C，D欄的值)
Sheet3模組

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, [B3:B100]) Is Nothing Or Target = "" Then Exit Sub
Application.EnableEvents = False
If Target.Offset(, 1) = "" Then Target.Offset(, 1).Resize(, 2) = Array(IIf(Target = "PD", 1, ""), IIf(Target = "PD", Date, ""))
Application.EnableEvents = True
End Sub

複製代碼

每次開檔時計算D欄日期到當日的天數並更改D欄為當日日期

Private Sub Workbook_Open()
Dim A As Range
With Sheet3
Application.EnableEvents = False
For Each A In .[B3:B100]
If A = "PD" And IsDate(A.Offset(, 2)) Then _
A.Offset(, 1) = _
DateDiff("d", A.Offset(, 2), Date) + A.Offset(, 1): A.Value = "CL": A.Offset(, 2) = Date
Next
Application.EnableEvents = True
End With
End Sub

複製代碼

計算pending day.zip (11.84 KB)

學海無涯_不恥下問

TOP

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

6^#

發表於 2010-11-27 17:03 | 只看該作者

回復 5# naraohya
1. 不太明白Resize(98)在此是指做了什麼動作?
你的附檔是指 Range("B3") 有 "PD" 字串時 -> [c3].Resize(98)=計算日數
[c3].Resize(98)-> 含c3擴充98列 =c3:c100的範圍
2. 這段程式似乎只有在同一列，在B3, C3才可運行!?
請附檔看看

TOP

naraohya

小學生 Rank: 1

帖子: 8
主題: 1
精華: 0
積分: 45
點名: 0
作業系統: XP
軟體版本: Office 2007
閱讀權限: 10
性別: 女
註冊時間: 2010-11-25
最後登錄: 2011-6-30

5^#

發表於 2010-11-26 22:24 | 只看該作者

回復 4# GBKEE

不好意思，GBKEE大，小的還是未試出來....
有確定將程式碼放在Worksheet_Change事件程序裡，
去切換B欄選單為PD時都未有動靜@@
1. 不太明白Resize(98)在此是指做了什麼動作?
2. 這段程式似乎只有在同一列，在B3, C3才可運行!?
但我需要若B欄有設定PD時，隔壁的C欄就會開始計算累加......
再麻煩提點，感謝！

TOP

GBKEE