Using Time Value As Range

**bdb1974** · 10-09-2009, 04:32 PM

Is there a way to specify a time range to do a specific task?

If Time Value is before 10:00:00 or between 13:00:00 and 16:00:00 Then
Do 'THis.

   If TimeValue.Now() <> TimeValue("10:00:00", "16:00:00") Then
   Workbooks("Inventory_New.xls").Activate
 With ActiveWorkbook
    Sheets("Sheet3").Activate
  Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("A1:IR" & WorksheetFunction.Max(9, Lastrow))
    .ClearContents
          End With
          End With

OR

   If TimeValue.Now() <TimeValue("10:00:00") or _
TimeValue.Now() >TimeValue("16:00:00") Then
   Workbooks("Inventory_New.xls").Activate
 With ActiveWorkbook
    Sheets("Sheet3").Activate
  Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("A1:IR" & WorksheetFunction.Max(9, Lastrow))
    .ClearContents
          End With
          End With

**shg** · 10-09-2009, 05:27 PM

What are you trying to do?

**bdb1974** · 10-09-2009, 05:54 PM

SHG,

I'm bringing external data into my worksheet which uses the 1st twelve rows. Everytime I run an update the old data in my worksheet
gets shifted to right and the new set of data goes into the 1st twelve rows.

I want to try and rewrite my macro so If I run an update between certain times of the day, the data in the 1st twelve rows will not get shifted but just get replaced. If the update is run outside of the time range,the data will get shifted. So no matter how many times I want to run an update, I will end up with only two sets of data for any given day on the sheet.
Therefore if an update runs from:
8:00 am to 9:59 am data does not get shifted.
10:00 am to 12:00 pm data gets shifted
12:01pm to 3:29 pm data does not get shifted
3:30pm to 7:79am next day, ( Reality will be updates will happen 3:30 pm to 5:00pm data gets shift)

If updates are run more than once during the time frame data gets shifted, I will have to build in some conditions to allow only 1 shift to occur.

Hope this makes sense.

Thanks,

BDB

**shg** · 10-10-2009, 11:22 AM

I don't see any difference in your code. Assuming you know how to do what you want in each case,

    Dim iShift      As Long
    Dim dTim        As Double
    
    Workbooks("Inventory_New.xls").Activate
    Worksheets("Sheet3").Select
    
    dTim = Time
    iShift = Evaluate("index({1,0,1,0,1}, match(" & dTim & ", {0,8,10,12,15.5}/24) )")

    If iShift Then
        ' do it one way
    Else
        ' do it the other
    End If

**bdb1974** · 10-12-2009, 10:20 AM

SHG,

First, thanks for your sharing your wealth of knowledge and skills to write code.
I plugged your code into my macro with no problems.

Can you please explain how the time shift works.
My reasoning is that it is for any given or set value,
Values being:0,8,10,12,15.5
within the range,
Range being : /24
You can set the code to run ' this way
else it will run ' that way if it not exactly at one of the given times.

Does this mean for the time shifts to work,'this way, the code will have to run exactly at the given intervals?

If so, is there a way to amend it so the time shifts can cover larger spans of time?

{0,8 to 10 to 12 15.5 to 18}/24) ?????

Anythoughts or inputs welcome.

Thanks again.

It always pumps me up with adrenaline to have a thought or an idea get turned into reality by the touch of one's brilliant abilities to make it happen.