我的if语句似乎不起作用。(My if statements don't seem to work. Might be an alternate way?)
所以,其基本要点是我每小时运行一次报告,并根据一天中的某个时间将结果填入特定单元格中。 现在我正在使用if语句来告诉它要填充哪些字段,但我不确定我是否正确地做了这件事。 我绝对每小时运行一次,因此代码不必基于一天中的什么时间,只要它不覆盖或删除已存在的内容,并且每次运行时都会移至下一行。 我刚刚包含了我的代码的顶部以及if语句的一个实例。 在第一个复制和粘贴整个剩下的部分,根据需要更改变量。 请让我知道如果我做错了什么,或者有更好的方法来做到这一点!
Sub Update() Dim sht As Worksheet Dim lastRow As Long lastRow = ActiveSheet.UsedRange.Rows.Count Set sht = ThisWorkbook.Worksheets("Sheet1") Dim path As String path = "C:\Users\Redacted\Desktop\Booking Window Avai -working copy.xlsm" Dim currentWb As Workbook Set currentWb = ThisWorkbook Dim openWb As Workbook Set openWb = Workbooks.Open(path) Dim openWs As Worksheet Set openWs = openWb.Sheets("Mail Format") Dim rng_data As Range Set rng_data = openWs.Range("B17") If ("C2") = "" And Now() > ("09:00") And Now() < ("10:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C2").PasteSpecial xlPasteValues] ElseIf ("C3") = "" And Now() > ("10:00") And Now() < ("11:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C3").PasteSpecial xlPasteValues] ElseIf ("C4") = "" And Now() > ("11:00") And Now() < ("12:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C4").PasteSpecial xlPasteValues] ElseIf ("C5") = "" And Now() > ("12:00") And Now() < ("13:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C5").PasteSpecial xlPasteValues] ElseIf ("C6") = "" And Now() > ("13:00") And Now() < ("14:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C6").PasteSpecial xlPasteValues] ElseIf ("C7") = "" And Now() > ("14:00") And Now() < ("15:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C7").PasteSpecial xlPasteValues] ElseIf ("C8") = "" And Now() > ("15:00") And Now() < ("16:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C8").PasteSpecial xlPasteValues] ElseIf ("C9") = "" And Now() > ("16:00") And Now() < ("17:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C9").PasteSpecial xlPasteValues] End If
So, the basic gist of this is that I run a report once an hour and I need it to fill the results into specific cells depending on what time of day it is. Right now I'm using if statements to tell it which fields to fill, but I'm not sure I'm doing it right. I definitely run it every hour so the code doesn't have to be based on what time of day it is as long as it doesn't overwrite or delete what is already there and will move to the next row each time it's run. I've just included the top of my code as well as one instance of the if statements. After the first one I copied and pasted throughout the rest changing the variables as needed. Please let me know if I'm doing something wrong or if there is a better way to do this!
Sub Update() Dim sht As Worksheet Dim lastRow As Long lastRow = ActiveSheet.UsedRange.Rows.Count Set sht = ThisWorkbook.Worksheets("Sheet1") Dim path As String path = "C:\Users\Redacted\Desktop\Booking Window Avai -working copy.xlsm" Dim currentWb As Workbook Set currentWb = ThisWorkbook Dim openWb As Workbook Set openWb = Workbooks.Open(path) Dim openWs As Worksheet Set openWs = openWb.Sheets("Mail Format") Dim rng_data As Range Set rng_data = openWs.Range("B17") If ("C2") = "" And Now() > ("09:00") And Now() < ("10:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C2").PasteSpecial xlPasteValues] ElseIf ("C3") = "" And Now() > ("10:00") And Now() < ("11:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C3").PasteSpecial xlPasteValues] ElseIf ("C4") = "" And Now() > ("11:00") And Now() < ("12:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C4").PasteSpecial xlPasteValues] ElseIf ("C5") = "" And Now() > ("12:00") And Now() < ("13:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C5").PasteSpecial xlPasteValues] ElseIf ("C6") = "" And Now() > ("13:00") And Now() < ("14:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C6").PasteSpecial xlPasteValues] ElseIf ("C7") = "" And Now() > ("14:00") And Now() < ("15:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C7").PasteSpecial xlPasteValues] ElseIf ("C8") = "" And Now() > ("15:00") And Now() < ("16:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C8").PasteSpecial xlPasteValues] ElseIf ("C9") = "" And Now() > ("16:00") And Now() < ("17:00") Then rng_data.Copy [currentWb.Sheets("sht").Range("C9").PasteSpecial xlPasteValues] End If
原文:https://stackoverflow.com/questions/49732831