使用现有代码更新2个单独的工作表
使用现有代码更新2个单独的工作表
提问于 2018-06-01 17:56:05
因此，我为我们的销售团队创建了一个生产力跟踪器，基于他们每小时打电话的数量，基于一个简单的按钮。我使用的代码决定了它是哪一天，它是一天中的哪一小时，然后每次按下按钮就增加一个。它也考虑到我们的午餐时间以及一天结束和开始的时间。然而，我现在面临的问题是，它要求他们总是在特定的名单上，让他们看到它。我更新了代码，以便他们可以从另一个工作表中按下按钮，但是我想让他们能够看到每天递增的总数。这是我正在使用的现有代码。
Sub OneclickUpdate()
strTime = Hour(Now()) 'grabs the current hour on a 24 hour rotation
Dim LWeekday As Integer
LWeekday = Weekday(Date, vbMonday) 'Set Monday as day 1
'Monday
If strTime = 9 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C3").Value = Worksheets("Productivity Tracker").Range("C3") + 1
ElseIf strTime = 10 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C4").Value = Worksheets("Productivity Tracker").Range("C4") + 1
ElseIf strTime = 11 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C5").Value = Worksheets("Productivity Tracker").Range("C5") + 1
ElseIf strTime = 12 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C6").Value = Worksheets("Productivity Tracker").Range("C6") + 1
ElseIf strTime = 13 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C7").Value = Worksheets("Productivity Tracker").Range("C7") + 1
ElseIf strTime = 14 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C7").Value = Worksheets("Productivity Tracker").Range("C7") + 1
ElseIf strTime = 15 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C9").Value = Worksheets("Productivity Tracker").Range("C9") + 1
ElseIf strTime = 16 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C10").Value = Worksheets("Productivity Tracker").Range("C10") + 1
ElseIf strTime = 17 And LWeekday = 1 Then
Worksheets("Productivity Tracker").Range("C11").Value = Worksheets("Productivity Tracker").Range("C11") + 1
'Tuesday
ElseIf strTime = 9 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E3").Value = Worksheets("Productivity Tracker").Range("E3") + 1
ElseIf strTime = 10 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E4").Value = Worksheets("Productivity Tracker").Range("E4") + 1
ElseIf strTime = 11 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E5").Value = Worksheets("Productivity Tracker").Range("E5") + 1
ElseIf strTime = 12 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E6").Value = Worksheets("Productivity Tracker").Range("E6") + 1
ElseIf strTime = 13 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E7").Value = Worksheets("Productivity Tracker").Range("E7") + 1
ElseIf strTime = 14 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E7").Value = Worksheets("Productivity Tracker").Range("E7") + 1
ElseIf strTime = 15 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E9").Value = Worksheets("Productivity Tracker").Range("E9") + 1
ElseIf strTime = 16 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E10").Value = Worksheets("Productivity Tracker").Range("E10") + 1
ElseIf strTime = 17 And LWeekday = 2 Then
Worksheets("Productivity Tracker").Range("E11").Value = Worksheets("Productivity Tracker").Range("E11") + 1
'Wednesday
ElseIf strTime = 9 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G3").Value = Worksheets("Productivity Tracker").Range("G3") + 1
ElseIf strTime = 10 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G4").Value = Worksheets("Productivity Tracker").Range("G4") + 1
ElseIf strTime = 11 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G5").Value = Worksheets("Productivity Tracker").Range("G5") + 1
ElseIf strTime = 12 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G6").Value = Worksheets("Productivity Tracker").Range("G6") + 1
ElseIf strTime = 13 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G7").Value = Worksheets("Productivity Tracker").Range("G7") + 1
ElseIf strTime = 14 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G7").Value = Worksheets("Productivity Tracker").Range("G7") + 1
ElseIf strTime = 15 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G9").Value = Worksheets("Productivity Tracker").Range("G9") + 1
ElseIf strTime = 16 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G10").Value = Worksheets("Productivity Tracker").Range("G10") + 1
ElseIf strTime = 17 And LWeekday = 3 Then
Worksheets("Productivity Tracker").Range("G11").Value = Worksheets("Productivity Tracker").Range("G11") + 1
'Thursday
ElseIf strTime = 9 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I3").Value = Worksheets("Productivity Tracker").Range("I3") + 1
ElseIf strTime = 10 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I4").Value = Worksheets("Productivity Tracker").Range("I4") + 1
ElseIf strTime = 11 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I5").Value = Worksheets("Productivity Tracker").Range("I5") + 1
ElseIf strTime = 12 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I6").Value = Worksheets("Productivity Tracker").Range("I6") + 1
ElseIf strTime = 13 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I7").Value = Worksheets("Productivity Tracker").Range("I7") + 1
ElseIf strTime = 14 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I7").Value = Worksheets("Productivity Tracker").Range("I7") + 1
ElseIf strTime = 15 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I9").Value = Worksheets("Productivity Tracker").Range("I9") + 1
ElseIf strTime = 16 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I10").Value = Worksheets("Productivity Tracker").Range("I10") + 1
ElseIf strTime = 17 And LWeekday = 4 Then
Worksheets("Productivity Tracker").Range("I11").Value = Worksheets("Productivity Tracker").Range("I11") + 1
'Friday
ElseIf strTime = 8 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K2").Value = Worksheets("Productivity Tracker").Range("K2") + 1
ElseIf strTime = 9 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K3").Value = Worksheets("Productivity Tracker").Range("K3") + 1
ElseIf strTime = 10 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K4").Value = Worksheets("Productivity Tracker").Range("K4") + 1
ElseIf strTime = 11 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K5").Value = Worksheets("Productivity Tracker").Range("K5") + 1
ElseIf strTime = 12 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K6").Value = Worksheets("Productivity Tracker").Range("K6") + 1
ElseIf strTime = 13 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K6").Value = Worksheets("Productivity Tracker").Range("K6") + 1
ElseIf strTime = 14 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K8").Value = Worksheets("Productivity Tracker").Range("K8") + 1
ElseIf strTime = 15 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K9").Value = Worksheets("Productivity Tracker").Range("K9") + 1
ElseIf strTime = 16 And LWeekday = 5 Then
Worksheets("Productivity Tracker").Range("K10").Value = Worksheets("Productivity
Tracker").Range("K10") + 1
ElseIf strTime <> [8,9,10,11,12,13,14,15,16,17] Or LWeekday <> [1,2,3,4,5] Then
MsgBox "Seriously, Enough work already, go home!"
End If
End Sub因此，假设这个生产力表是工作表B，我希望能把单张A的单元格A1放在白天，在单元格B1中放A页，它到目前为止已经增加了一个数。我希望这是有意义的。非常感谢你的帮助。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-01 19:40:53
似乎您只想知道不同工作表上的总数，我将工作表命名为“工作表A”，并采取了一种不同的方法来缩短代码。
试试这个：
Sub OneclickUpdate()
strTime = Hour(Now()) 'grabs the current hour on a 24 hour rotation
Dim LWeekday As Integer, count As Integer
Dim cols As Variant
usedCol = Array("C", "E", "G", "I", "K")
weekdayRow = Array(3, 4, 5, 6, 7, 7, 9, 10, 11)
weekendRow = Array(2, 3, 4, 5, 6, 6, 8, 9, 10)
LWeekday = Weekday(Date, vbMonday) 'Set Monday as day 1
If strTime >= 8 And strTime <= 17 And LWeekday >= 1 And LWeekday <= 5 Then
'The next two lines paste the date on A1 in Sheet A and Sheet B
Worksheets("SHEET A").Range("A1").Value = Format(Now(), "mm/dd/yyyy")
Worksheets("SHEET B").Range("A1").Value = Format(Now(), "mm/dd/yyyy")
If LWeekday = 5 And strTime <> 17 Then
With Worksheets("Productivity Tracker").Range(usedCol(LWeekday - 1) & weekendRow(strTime - 8))
.Value = .Value + 1
'The next two lines paste the Total number for the day on
'Sheet A and Sheet B
Worksheets("SHEET A").Range("B1").Value = .Value
Worksheets("SHEET B").Range("B1").Value = .Value
End With
ElseIf LWeekday <> 5 And strTime <> 8 Then
With Worksheets("Productivity Tracker").Range(usedCol(LWeekday - 1) & weekdayRow(strTime - 9))
.Value = .Value + 1
'The next two lines paste the Total number for the day on
'Sheet A and Sheet B
Worksheets("SHEET A").Range("B1").Value = .Value
Worksheets("SHEET B").Range("B1").Value = .Value
End With
Else
MsgBox "Seriously, Enough work already, go home!"
End If
Else
MsgBox "Seriously, Enough work already, go home!"
End If
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50648750
复制相关文章
相似问题
腾讯云开发者
