我似乎被困在看起來非常簡單的代碼上。我對 VB 很陌生，所以如果我做了一些非常愚蠢的事情，請耐心等待。
這是一個時間表，我記錄了我全年每天的開始和結束時間。
所以每年我都會制作一份 Excel 檔案的新副本，我想覆寫我進入上一年的時間，默認開始時間是早上 7 點，下午 4:30 我可能可以直接在 Excel 中使用“Vlookup”來完成，但是這個時間表是一個提高 VBA 技能的專案
“開始新作業表”按鈕打開一個用戶表單，用于其他一些一切正常的輸入。
在 Private Sub TSSubmitButton_Click() 中，我已經定義了我需要的范圍和字串并做了一些For Each Cell in Range Find Monday，然后寫入附加到星期一開始時間 (07:00:00) 和星期一結束時間 (16:45:00) 的變數
（例如僅包括星期一和星期二）
除錯不會標記任何錯誤，并且當代碼運行時（即單擊用戶表單提交按鈕）沒有任何反應，所有時間值都保持原樣。我試過把“星期一作為字串”變成“星期一作為日期”我試過沒有Find(DayCell.Value)
Private Sub TSSubmitButton_Click()
' Definitions
Dim DayRange As Range
Dim DayCell As Range
Set DayRange = Sheet1.Range("A2:A426")
Set DayCell = DayRange(1, 1)
Dim MonStartTime As String
Dim MonFinishTime As String
Dim TueStartTime As String
Dim TueFinishTime As String
Dim WedStartTime As String
Dim WedFinishTime As String
Dim ThuStartTime As String
Dim ThuFinishTime As String
Dim FriStartTime As String
Dim FriFinishTime As String
Dim SatStartTime As String
Dim SatFinishTime As String
Dim SunStartTime As String
Dim SunFinishTime As String
Dim Monday As String
Dim Tuesday As String
Dim Wednesday As String
Dim Thursday As String
Dim Friday As String
Dim Satday As String
Dim Sunday As String
MonStartTime = "07:00:00"
MonFinishTime = "16:45:00"
TueStartTime = "07:00:00"
TueFinishTime = "16:45:00"
WedStartTime = "07:00:00"
WedFinishTime = "16:45:00"
ThuStartTime = "07:00:00"
ThuFinishTime = "16:45:00"
FriStartTime = "00:00:00"
FriFinishTime = "00:00:00"
SatStartTime = "00:00:00"
SatFinishTime = "00:00:00"
SunStartTime = "00:00:00"
SunFinishTime = "00:00:00"
' loops the if statement through all cells and sets the time in adjecnt cells
For Each DayCell In DayRange.Find(DayCell.Value)
If DayCell = Monday Then
DayCell.Offset(, 2).Value = MonStartTime
DayCell.Offset(, 7).Value = MonFinishTime
End If
' loops the if statement through all cells and sets the time in adjecnt cells
For Each DayCell In DayRange.Find(DayCell.Value)
If DayCell = Tuesday Then
DayCell.Offset(, 2).Value = TueStartTime
DayCell.Offset(, 7).Value = TueFinishTime
End If
Next
Unload Me
End Sub
uj5u.com熱心網友回復：
我不知道你是否會喜歡這個解決方案！
- 我將您的時間表范圍轉換為表格物件
- 我為作業日的開始和結束時間創建了一個表格
- 使用 XLOOKUP 公式，我可以得到開始和結束列的開始和結束時間。您可以使用 VLOOKUP 或 INDEX & MATCH 而不是 XLOOKUP
Sub ResetTimesheet()
Dim ws As Worksheet
Dim olWkDay As ListObject
Dim olTimetable As ListObject
Dim olCol As Long
Dim olRng As Range
Set ws = ActiveSheet
'Tables
Set olWkDay = ws.ListObjects("tbWkDayTime")
Set olTimetable = ws.ListObjects("tbTimetable")
''''''''''''''''''''''''''''''''''''''''''''
' Start column
''''''''''''''''''''''''''''''''''''''''''''
olCol = olTimetable.ListColumns("Start").Index
Set olRng = olTimetable.ListColumns(olCol).DataBodyRange
' Clear column contents
olRng.ClearContents
' Apply formula: you can use VLookup or Index & Match
olRng.Formula2R1C1 = "=XLOOKUP([@Weekday],tbWkDayTime[Weekday],tbWkDayTime[StartTime])"
' Copy to values
olRng.Copy
olRng.PasteSpecial xlPasteValues
Application.CutCopyMode = False
''''''''''''''''''''''''''''''''''''''''''''
' Finish column
''''''''''''''''''''''''''''''''''''''''''''
olCol = olTimetable.ListColumns("Finish").Index
Set olRng = olTimetable.ListColumns(olCol).DataBodyRange
' Clear column contents
olRng.ClearContents
' Apply formula: you can use VLookup or Index & Match
olRng.Formula2R1C1 = "=XLOOKUP([@Weekday],tbWkDayTime[Weekday],tbWkDayTime[FinishTime])"
' Copy to values
olRng.Copy
olRng.PasteSpecial xlPasteValues
Application.CutCopyMode = False
' Clear
Set olWkDay = Nothing
Set olTimetable = Nothing
End Sub
像這樣改變For..Next
For Each DayCell In DayRange
If DayCell.Value = "Monday" Then DayCell.Offset(, 2).Value = MonStartTime: DayCell.Offset(, 5).Value = MonFinishTime: GoTo nextDay
If DayCell.Value = "Tuesday" Then DayCell.Offset(, 2).Value = MonStartTime: DayCell.Offset(, 5).Value = MonFinishTime: GoTo nextDay
nextDay:
Next
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/390955.html