Board logo

標題: [發問] 請問如何撰寫時間遞減的程式碼? [打印本頁]
作者: av8d    時間: 2022-8-17 00:49     標題: 請問如何撰寫時間遞減的程式碼?

預計會寫到30，寫到了7發現程式碼好長，我這裡也會去爬文看看如何撰寫，謝謝前輩。
  1.     N0 = Now
  2.     yTD0 = Format(N0, "yyyy")
  3.     mTD0 = Format(N0, "m")
  4.     dTD0 = Format(N0, "dd")
  5.     CBN0 = yTD0 & "/" & mTD0 & "/" & dTD0
  6.    
  7.     N1 = Now - 1
  8.     yTD1 = Format(N1, "yyyy")
  9.     mTD1 = Format(N1, "m")
  10.     dTD1 = Format(N1, "dd")
  11.     CBN1 = yTD1 & "/" & mTD1 & "/" & dTD1
  12.    
  13.     N2 = Now - 2
  14.     yTD2 = Format(N2, "yyyy")
  15.     mTD2 = Format(N2, "m")
  16.     dTD2 = Format(N2, "dd")
  17.     CBN2 = yTD2 & "/" & mTD2 & "/" & dTD2
  18.    
  19.     N3 = Now - 3
  20.     yTD3 = Format(N3, "yyyy")
  21.     mTD3 = Format(N3, "m")
  22.     dTD3 = Format(N3, "dd")
  23.     CBN3 = yTD3 & "/" & mTD3 & "/" & dTD3
  24.    
  25.     N4 = Now - 4
  26.     yTD4 = Format(N4, "yyyy")
  27.     mTD4 = Format(N4, "m")
  28.     dTD4 = Format(N4, "dd")
  29.     CBN4 = yTD4 & "/" & mTD4 & "/" & dTD4
  30.    
  31.     N5 = Now - 5
  32.     yTD5 = Format(N5, "yyyy")
  33.     mTD5 = Format(N5, "m")
  34.     dTD5 = Format(N5, "dd")
  35.     CBN5 = yTD5 & "/" & mTD5 & "/" & dTD5
  36.    
  37.     N6 = Now - 6
  38.     yTD6 = Format(N6, "yyyy")
  39.     mTD6 = Format(N6, "m")
  40.     dTD6 = Format(N6, "dd")
  41.     CBN6 = yTD6 & "/" & mTD6 & "/" & dTD6
  42.    
  43.     N7 = Now - 7
  44.     yTD7 = Format(N7, "yyyy")
  45.     mTD7 = Format(N7, "m")
  46.     dTD7 = Format(N7, "dd")
  47.     CBN7 = yTD7 & "/" & mTD7 & "/" & dTD7
  48.    
  49.     With Sheets("系統檔").ComboBox1
  50.         .AddItem CBN0
  51.         .AddItem CBN1
  52.         .AddItem CBN2
  53.         .AddItem CBN3
  54.         .AddItem CBN4
  55.         .AddItem CBN5
  56.         .AddItem CBN6
  57.         .AddItem CBN7
  58.     End With
複製代碼
作者: singo1232001    時間: 2022-8-17 02:55

本帖最後由 singo1232001 於 2022-8-17 02:56 編輯

回復 1# av8d


Sub test2()
Sheets("系統檔").ComboBox1.Clear
For i = 1 To 7
Sheets("系統檔").ComboBox1.AddItem Date - i
Next
End Sub



如果要有0

Sub test3()
Sheets("系統檔").ComboBox1.Clear
For i = 1 To 7
Sheets("系統檔").ComboBox1.AddItem Format(Date - i, "yyyy/mm/dd")
Next
End Sub
作者: av8d    時間: 2022-8-17 13:59

本帖最後由 av8d 於 2022-8-17 14:11 編輯

回復 2# singo1232001


感謝您抽空解答，我修改了條件，也沒問題，但是修改到雙條件，則出現問題，不知問題在哪。

For i = 1 To 7
    If WeekdayName(Weekday(Date - i)) <> "星期六" Then
        Sheets("系統檔").ComboBox1.AddItem Date - i
    End If
Next

以上單一條件星期六省略，則沒問題，但是以下雙條件，則出現了雙條件都不會被省略的問題。

For i = 1 To 7
    If WeekdayName(Weekday(Date - i)) <> "星期六" Or WeekdayName(Weekday(Date - i)) <> "星期日" Then
        Sheets("系統檔").ComboBox1.AddItem Date - i
    End If
Next


問題二、
當改寫為以今天為主，前後7天，然後正常順序排列，也會出現沒有正常排序的問題，目前在考慮是否寫在儲存格中，然後再順向讀取一次。

For i = 1 To 7
    Sheets("系統檔").ComboBox1.AddItem Date + i
Next
For i = 1 To 7
    Sheets("系統檔").ComboBox1.AddItem Date - i
Next
作者: av8d    時間: 2022-8-17 14:31

回復 2# singo1232001

問題一已解開，剩下問題二，十分感謝！

問題二、
當改寫為以今天為主，前後7天，然後正常順序排列，也會出現沒有正常排序的問題，目前在考慮是否寫在儲存格中，然後再順向讀取一次。

For i = 1 To 7
    Sheets("系統檔").ComboBox1.AddItem Date + i
Next
For i = 1 To 7
    Sheets("系統檔").ComboBox1.AddItem Date - i
Next
作者: av8d    時間: 2022-8-17 14:40

回復 2# singo1232001


    全部問題已解開，萬分感謝前輩的提攜，受益良多，萬分感謝！

以下是問題二的解法~

For i = 7 To 1 Step -1
    If WeekdayName(Weekday(Date - i)) = "星期二" Or WeekdayName(Weekday(Date - i)) = "星期五" Then
        Sheets("系統檔").ComboBox1.AddItem Date - i
    End If
Next
For i = 1 To 7
    If WeekdayName(Weekday(Date + i)) = "星期二" Or WeekdayName(Weekday(Date + i)) = "星期五" Then
        Sheets("系統檔").ComboBox1.AddItem Date + i
    End If
Next
作者: singo1232001    時間: 2022-8-17 22:37

本帖最後由 singo1232001 於 2022-8-17 22:45 編輯

回復 5# av8d


Sub test()
Sheets("系統檔").ComboBox1.Clear
For i = 7 To -7 Step -1
     wdi = Weekday(Date - i, 2)
    If wdi = 2 Or wdi = 5 Then
        Sheets("系統檔").ComboBox1.AddItem Format(Date - i, "yyyy/m/d") & " " & WeekdayName(wdi, , 2)
        'Sheets("系統檔").ComboBox1.AddItem Format(Date - i, "yyyy/m/d")  '沒星期幾
    End If
Next
End Sub

'要注意當日 撞上星期二與星期五的問題 這種寫法是當日星期二或星期五也會找到   包括後面顯示星期幾
作者: av8d    時間: 2022-8-18 16:28

回復 6# singo1232001


前輩有您的改寫，我進步好多，無論是範圍、指定日期都大大提升撰寫的方便性，以下是我最後改寫出來的成果，謝謝您！

For i = 11 To -11 Step -1
    wdi = Weekday(Date - i, 2)
    If wdi >= 1 And wdi <= 5 Then
        Sheets("系統檔").ComboBox1.AddItem Format(Date - i, "yyyy/m/d") & " " & WeekdayName(wdi, 1, 2)
        'Sheets("系統檔").ComboBox1.AddItem Format(Date - i, "yyyy/m/d")  '沒星期幾
    End If
Next
wd = Weekday(Date, 2)
wd4 = Weekday(Date + 4, 2)
wd3 = Weekday(Date + 3, 2)
wd2 = Weekday(Date + 2, 2)
wd1 = Weekday(Date + 1, 2)
If wd = 1 Or wd = 5 Then Sheets("系統檔").ComboBox1 = Format(Date + 4, "yyyy/m/d") & " " & WeekdayName(wd4, 1, 2)
If wd = 2 Or wd = 6 Then Sheets("系統檔").ComboBox1 = Format(Date + 3, "yyyy/m/d") & " " & WeekdayName(wd3, 1, 2)
If wd = 3 Then Sheets("系統檔").ComboBox1 = Format(Date + 2, "yyyy/m/d") & " " & WeekdayName(wd2, 1, 2)
If wd = 4 Then Sheets("系統檔").ComboBox1 = Format(Date + 1, "yyyy/m/d") & " " & WeekdayName(wd1, 1, 2)
作者: singo1232001    時間: 2022-8-18 23:43

本帖最後由 singo1232001 於 2022-8-18 23:52 編輯

回復 7# av8d


Sub test()
Set Sc = Sheets("系統檔").ComboBox1
Sc.Clear
For i = 11 To -11 Step -1
  w = Weekday(Date - i, 2)
  If w >= 1 And w <= 5 Then Sc.AddItem Format(Date - i, "yyyy/m/d") & " " & WeekdayName(w, 1, 2)
Next

Select Case Weekday(Date, 2)
Case Is = 1, 5: Sc.Text = Format(Date + 4, "yyyy/m/d") & " " & WeekdayName(Weekday(Date + 4, 2), 1, 2)
Case Is = 2, 6: Sc.Text = Format(Date + 3, "yyyy/m/d") & " " & WeekdayName(Weekday(Date + 3, 2), 1, 2)
Case Is = 3: Sc.Text = Format(Date + 2, "yyyy/m/d") & " " & WeekdayName(Weekday(Date + 2, 2), 1, 2)
Case Is = 4: Sc.Text = Format(Date + 1, "yyyy/m/d") & " " & WeekdayName(Weekday(Date + 1, 2), 1, 2)
End Select
End Sub

1.  Sheets("系統檔").ComboBox1 可以縮寫成你想要到名稱
2.多條件判斷 可以練習使用select case   當然if也沒問題
會用的原因是 當多條件內部  未來出現多層判斷時 可以select case + if then 搭配 比較好分辨

若完成後 也可以直接全部使用with Sheets("系統檔").ComboBox1 的方式全包起來



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