CODEWITHSUNDEEP
excelvbaif-statement
Jb83
Jb83

Reputation: 147

My if statements don't seem to work. Might be an alternate way?

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

Upvotes: 1

Views: 58

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

If you have the time values in a column on Sheet1 it will be much easier to move the values across. This assumes that column B contains time values - 09:00, 10:00, etc.

Sub Update()

    Dim openWb As Workbook
    Dim rng_data As Range
    Dim sht_Target As Worksheet
    Dim lPasteRow As Long

    Set sht_Target = ThisWorkbook.Worksheets("Sheet1")

    Set openWb = Workbooks.Open("C:\Users\Redacted\Desktop\Booking Window Avai -working copy.xlsm")
    Set rng_data = openWb.Worksheets("Mail Format").Range("B17")

    'This next row replaces your IF statements.
    lPasteRow = Application.Match(CDbl(Time()), sht_Target.Range("B:B"), 1)
    sht_Target.Cells(lPasteRow, 3) = rng_data.Value

End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43585

The Now() < ("15:00") does not do what your code expects. Take a look at the code below:

Sub TestMe()

    Debug.Print Now()                               '09.04.2018 14:23:56
    Debug.Print Now() < ("10:00")                   'True
    Debug.Print Now() < ("something meaningless")   'True
    Debug.Print Now() < ("")                        'False

                                                    'This is one way to do it--v
    Debug.Print TimeSerial(Hour(Now), Minute(Now), Second(Now)) < TimeSerial(10, 10, 0)

End Sub

As you see, you have to compare Values parsed to Date with Values parsed to Date. Otherwise it parses the date to a String and compares the strings.

TimeSerial is just one of the options. TimeSerial MSDN.

Upvotes: 4

Related Questions