Single calculation

J

Juan C

Board Regular
Joined
Mar 6, 2009
Messages
131
Help me here...
Is there a way for me to have a formula perform its calculation one time only... meaning that if the precedent data changes it (the formula) won't compute again, thus leaving the previous number it calculated unchange...
 
Sort by date Sort by votes
Yes I did... I even when further and did the work book using your same formula without changing or adding any sheets / names and it did not work....

VoG said:
I have no idea - it works for me. Did you save the workbook then re-open it?
Click to expand...
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
And this is the final work with all the sheets names and scheduled hours in:

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:time Hour="0" Minute="0">00:00:05</st1:time>")
Application.OnTime dTime, "MyMacro"
With Sheets("D 0-1")
If Now - Date >= TimeValue("<st1:time Hour="0" Minute="0">00:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="0" Minute="59">00:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 1-2")
If Now - Date >= TimeValue("<st1:time Hour="13" Minute="0">01:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="13" Minute="59">01:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End With
With Sheets("D 2-3")
If Now - Date >= TimeValue("<st1:time Hour="14" Minute="0">02:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="14" Minute="59">02:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 3-4")
If Now - Date >= TimeValue("<st1:time Hour="15" Minute="0">03:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="15" Minute="59">03:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 4-5")
If Now - Date >= TimeValue("<st1:time Hour="16" Minute="0">04:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="16" Minute="59">04:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 5-6")
If Now - Date >= TimeValue("<st1:time Hour="17" Minute="0">05:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="17" Minute="59">05:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 6-7")
If Now - Date >= TimeValue("<st1:time Hour="18" Minute="0">06:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="18" Minute="59">06:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 7-8")
If Now - Date >= TimeValue("<st1:time Hour="19" Minute="0">07:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="19" Minute="59">07:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 8-9")
If Now - Date >= TimeValue("<st1:time Hour="8" Minute="0">08:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="8" Minute="59">08:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 9-10")
If Now - Date >= TimeValue("<st1:time Hour="9" Minute="0">09:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="9" Minute="59">09:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 10-11")
If Now - Date >= TimeValue("<st1:time Hour="10" Minute="0">10:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="10" Minute="59">10:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 11-12")
If Now - Date >= TimeValue("<st1:time Hour="11" Minute="0">11:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="11" Minute="59">11:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 12-13")
If Now - Date >= TimeValue("<st1:time Hour="12" Minute="0">12:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="12" Minute="59">12:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 13-14")
If Now - Date >= TimeValue("<st1:time Hour="13" Minute="0">13:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="13" Minute="59">13:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 14-15")
If Now - Date >= TimeValue("<st1:time Hour="14" Minute="0">14:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="14" Minute="59">14:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 15-16")
If Now - Date >= TimeValue("<st1:time Hour="15" Minute="0">15:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="15" Minute="59">15:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 16-17")
If Now - Date >= TimeValue("<st1:time Hour="16" Minute="0">16:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="16" Minute="59">16:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 17-18")
If Now - Date >= TimeValue("<st1:time Hour="17" Minute="0">17:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="17" Minute="59">17:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 18-19")
If Now - Date >= TimeValue("<st1:time Hour="18" Minute="0">18:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="18" Minute="59">18:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 19-20")
If Now - Date >= TimeValue("<st1:time Hour="19" Minute="0">19:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="19" Minute="59">19:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 20-21")
If Now - Date >= TimeValue("<st1:time Hour="20" Minute="0">20:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="20" Minute="59">20:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 21-22")
If Now - Date >= TimeValue("<st1:time Hour="21" Minute="0">21:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="21" Minute="59">21:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 22-23")
If Now - Date >= TimeValue("<st1:time Hour="22" Minute="0">22:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="22" Minute="59">22:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
With Sheets("D 23-24")
If Now - Date >= TimeValue("<st1:time Hour="23" Minute="0">23:00:00</st1:time>") And Now - Date <= TimeValue("<st1:time Hour="23" Minute="59">23:59:59</st1:time>") Then
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End If
End Sub

Juan C said:
Yes I did... I even when further and did the work book using your same formula without changing or adding any sheets / names and it did not work....
Click to expand...
 
Upvote 0
Rich (BB code): 
Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("D 0-1")
    If Now - Date >= TimeValue("00:00:00") And Now - Date <= TimeValue("00:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 1-2")
    If Now - Date >= TimeValue("01:00:00") And Now - Date <= TimeValue("01:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 2-3")
    If Now - Date >= TimeValue("02:00:00") And Now - Date <= TimeValue("02:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 3-4")
    If Now - Date >= TimeValue("03:00:00") And Now - Date <= TimeValue("03:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 4-5")
    If Now - Date >= TimeValue("04:00:00") And Now - Date <= TimeValue("04:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 5-6")
    If Now - Date >= TimeValue("05:00:00") And Now - Date <= TimeValue("05:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 6-7")
    If Now - Date >= TimeValue("06:00:00") And Now - Date <= TimeValue("06:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 7-8")
    If Now - Date >= TimeValue("07:00:00") And Now - Date <= TimeValue("07:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 8-9")
    If Now - Date >= TimeValue("08:00:00") And Now - Date <= TimeValue("08:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 9-10")
    If Now - Date >= TimeValue("09:00:00") And Now - Date <= TimeValue("09:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 10-11")
    If Now - Date >= TimeValue("10:00:00") And Now - Date <= TimeValue("10:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 11-12")
    If Now - Date >= TimeValue("11:00:00") And Now - Date <= TimeValue("11:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 12-13")
    If Now - Date >= TimeValue("12:00:00") And Now - Date <= TimeValue("12:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 13-14")
    If Now - Date >= TimeValue("13:00:00") And Now - Date <= TimeValue("13:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 14-15")
    If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("14:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 15-16")
    If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("15:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 16-17")
    If Now - Date >= TimeValue("16:00:00") And Now - Date <= TimeValue("16:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 17-18")
    If Now - Date >= TimeValue("17:00:00") And Now - Date <= TimeValue("17:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 18-19")
    If Now - Date >= TimeValue("18:00:00") And Now - Date <= TimeValue("18:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 19-20")
    If Now - Date >= TimeValue("19:00:00") And Now - Date <= TimeValue("19:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 20-21")
    If Now - Date >= TimeValue("20:00:00") And Now - Date <= TimeValue("20:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 21-22")
    If Now - Date >= TimeValue("21:00:00") And Now - Date <= TimeValue("21:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 22-23")
    If Now - Date >= TimeValue("22:00:00") And Now - Date <= TimeValue("22:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 23-24")
    If Now - Date >= TimeValue("23:00:00") And Now - Date <= TimeValue("23:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
End Sub
 
Upvote 0
Dear GoV...

Good morning...

I copy - pasted these codes and nothing happens yet.... It begins populating the data in its specific timed sheet, but it just fills the first row, and then a note pops up and says: Compile Error: Expected: end of statement.

Any clues????

Thanks

JC
VoG said:
Rich (BB code): 
Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("D 0-1")
    If Now - Date >= TimeValue("00:00:00") And Now - Date <= TimeValue("00:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 1-2")
    If Now - Date >= TimeValue("01:00:00") And Now - Date <= TimeValue("01:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 2-3")
    If Now - Date >= TimeValue("02:00:00") And Now - Date <= TimeValue("02:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 3-4")
    If Now - Date >= TimeValue("03:00:00") And Now - Date <= TimeValue("03:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 4-5")
    If Now - Date >= TimeValue("04:00:00") And Now - Date <= TimeValue("04:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 5-6")
    If Now - Date >= TimeValue("05:00:00") And Now - Date <= TimeValue("05:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 6-7")
    If Now - Date >= TimeValue("06:00:00") And Now - Date <= TimeValue("06:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 7-8")
    If Now - Date >= TimeValue("07:00:00") And Now - Date <= TimeValue("07:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 8-9")
    If Now - Date >= TimeValue("08:00:00") And Now - Date <= TimeValue("08:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 9-10")
    If Now - Date >= TimeValue("09:00:00") And Now - Date <= TimeValue("09:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 10-11")
    If Now - Date >= TimeValue("10:00:00") And Now - Date <= TimeValue("10:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 11-12")
    If Now - Date >= TimeValue("11:00:00") And Now - Date <= TimeValue("11:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 12-13")
    If Now - Date >= TimeValue("12:00:00") And Now - Date <= TimeValue("12:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 13-14")
    If Now - Date >= TimeValue("13:00:00") And Now - Date <= TimeValue("13:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 14-15")
    If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("14:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 15-16")
    If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("15:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 16-17")
    If Now - Date >= TimeValue("16:00:00") And Now - Date <= TimeValue("16:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 17-18")
    If Now - Date >= TimeValue("17:00:00") And Now - Date <= TimeValue("17:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 18-19")
    If Now - Date >= TimeValue("18:00:00") And Now - Date <= TimeValue("18:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 19-20")
    If Now - Date >= TimeValue("19:00:00") And Now - Date <= TimeValue("19:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 20-21")
    If Now - Date >= TimeValue("20:00:00") And Now - Date <= TimeValue("20:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 21-22")
    If Now - Date >= TimeValue("21:00:00") And Now - Date <= TimeValue("21:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 22-23")
    If Now - Date >= TimeValue("22:00:00") And Now - Date <= TimeValue("22:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("D 23-24")
    If Now - Date >= TimeValue("23:00:00") And Now - Date <= TimeValue("23:59:59") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
End Sub
Click to expand...
 
Upvote 0
It also highlighted the word "dTime" at the very begining of the codes...


Juan C said:
Dear GoV...

Good morning...

I copy - pasted these codes and nothing happens yet.... It begins populating the data in its specific timed sheet, but it just fills the first row, and then a note pops up and says: Compile Error: Expected: end of statement.

Any clues????

Thanks

JC
Click to expand...
 
Upvote 0
Thanks... I'll do that... but I found that I had some old codes in one of the sheets. I removed it and now it says the following:

The Macro "C:\Docyments and Settings\..........!MyMacro' cannot be found.

If I ignore this message and hit run macro again, it will fill a line of data and then I'll get this message again...!!!

Any idea?

Thanks

JC


VoG said:
I really have no idea - the code compiles and runs fine for me.

I suggest that you hire a professional to sort this out - see http://www.mrexcel.com/consult.shtml
Click to expand...
 
Upvote 0
Hi VoG... sorry to bother again, but I've been trying to contact Mr. Excel on their emergency line (they are away) and I sent an email requesting their assistance, but have receive no response yet...

As I mentioned in my last post.... I'm not receiving a pop up sayng that "MyMacro cannot be found"

Any ideas (at least until I received help from Mr. Excel)....


Thanks

JC

Juan C said:
Thanks... I'll do that... but I found that I had some old codes in one of the sheets. I removed it and now it says the following:

The Macro "C:\Docyments and Settings\..........!MyMacro' cannot be found.

If I ignore this message and hit run macro again, it will fill a line of data and then I'll get this message again...!!!

Any idea?

Thanks

JC
Click to expand...
 
Upvote 0
If its possible, try putting everything into a brand new workbook...maybe a clean start will clear out something that's gummed up in memory.

1) Copy data and formulas to a new workbook as cells, not whole sheets
2) create a new module and copy code into the module and re-compile.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
348
Romy
R
B
  • Question
Excel 2021 - Sum cells that contain text for total and total by month
Replies
1
Views
97
bebo021999
B
M
  • Solved
Calculating 1 / 3 / 5 year Returns on a table that varies in length.
Replies
3
Views
306
Anthony47
A
J
  • Solved
Sum FTEs based on start/end date
Replies
3
Views
208
etaf
etaf
J
  • Question
Simple Excel Formulas Not Showing Correct Answer (shows Dash or 0 depending on formatting) NOT a "Numbers Stored As Text" issue!
Replies
6
Views
666
Alex Blakenburg
A

Forum statistics

Threads
1,215,514
Messages
6,125,267
Members
449,219
Latest member
daynle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top