none
Split time in minutes interval and look for value against each time in 2nd sheet data RRS feed

  • Question

  • Hi . 

    I have written a code to split time in minutes interval till the last date of month as follows

    Sub forLoop()
    Dim StDate As Long
    Dim TimeInterval As Integer
    Dim MinuteInterval As Integer
    Dim TimeStamp As Long
    Application.ScreenUpdating = False
    
    Range("C5").Value = "Date"
    Range("D5").Value = "Time"
    Range("E5").Value = "Time Stamp"
    Range("F5").Value = "Start Load"
    Range("G5").Value = "End Load"
    Range("C6").Select
    
    For StDate = Range("C3") To DateSerial(Year(Range("C3")), Month(Range("C3")) + 1, 1) - 1
    
        For TimeInterval = 0 To 23
            
            For MinuteInterval = 0 To 59
          
            ActiveCell.Value = StDate
            ActiveCell.Offset(0, 1).Value = TimeSerial((TimeInterval), (MinuteInterval), 0)
            ActiveCell.Offset(0, 2).Value = DateSerial(Year(StDate), Month(StDate), Day(StDate)) + TimeSerial((TimeInterval), (MinuteInterval), 0)
            ActiveCell.Offset(1, 0).Select
            Next MinuteInterval
            
            Next TimeInterval
            
    Next StDate
        Columns("C:C").NumberFormat = "dd.mmm.yyyy"
        Columns("D:D").NumberFormat = "h:mm;@"
        Columns("E:E").NumberFormat = "dd.mmm.yyyy hh:mm"
    Range("C5").Select
    Application.ScreenUpdating = True
    End Sub
    
    

    It gives output as follows in "Detailed Calculation" Sheet of workbook

    Now i want to insert Start load and end load against each cell value in Column "E" from other sheet named as "Load Data" . its screenshot is as follows.

    Can anybody may please help me to write a vba code for this.

    Saturday, August 27, 2016 11:35 AM

Answers

  • What happens if you change

    If Err.Number = 1004 Then

    to

    If Err.Number <> 0 Then

    Also, I'd change the line

    Resume Next

    to

    Err.Clear


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Friday, September 2, 2016 8:29 AM
    • Marked as answer by zubairg Wednesday, September 21, 2016 4:20 AM
    Sunday, August 28, 2016 11:18 AM

All replies

  • I have no idea what you want since you haven't included a screenshot, nor a clear description.

    But here is a slightly more efficient version of your macro; it avoids selecting cells.

    Sub forLoop()
        Dim StDate As Long
        Dim TimeInterval As Integer
        Dim MinuteInterval As Integer
        Dim TimeStamp As Long
        Dim r As Long
    
        Application.ScreenUpdating = False
        Range("C5").Value = "Date"
        Range("D5").Value = "Time"
        Range("E5").Value = "Time Stamp"
        Range("F5").Value = "Start Load"
        Range("G5").Value = "End Load"
    
        r = 6
        For StDate = Range("C3").Value To Application.EoMonth(Range("C3").Value, 0)
            For TimeInterval = 0 To 23
                For MinuteInterval = 0 To 59
                    Cells(r, 3).Value = StDate
                    Cells(r, 4).Value = TimeSerial(TimeInterval, MinuteInterval, 0)
                    Cells(r, 5).Value = Cells(r, 3).Value + Cells(r, 4).Value
                    r = r + 1
                Next MinuteInterval
            Next TimeInterval
        Next StDate
    
        Columns("C:C").NumberFormat = "dd.mmm.yyyy"
        Columns("D:D").NumberFormat = "hh:mm"
        Columns("E:E").NumberFormat = "dd.mmm.yyyy hh:mm"
    
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, August 27, 2016 1:53 PM
  • Many thanks for your kind reply and code correction.

    I have somehow managed to get the output i wanted through this code.

    Sub forLoop()
        Dim StDate As Long
        Dim TimeInterval As Integer
        Dim MinuteInterval As Integer
        Dim TimeStamp As Long
        Dim r As Long
        Dim StartLoad As String
       ' Dim StartLoad As String
        Dim EndLoad As String
        Dim Ramprate As String
        
        Application.ScreenUpdating = False
        Range("C5").Value = "Date"
        Range("D5").Value = "Time"
        Range("E5").Value = "Time Stamp"
        Range("F5").Value = "Start Load"
        Range("G5").Value = "End Load"
        Range("H5").Value = "Ramp Rate"
    
        r = 6
        For StDate = Range("C3").Value To Application.EoMonth(Range("C3").Value, 0)
            For TimeInterval = 0 To 23
                For MinuteInterval = 0 To 59
                    Cells(r, 3).Value = StDate
                    Cells(r, 4).Value = TimeSerial(TimeInterval, MinuteInterval, 0)
                    Cells(r, 5).Value = Cells(r, 3).Value + Cells(r, 4).Value
                            
                            On Error GoTo Errorhandler:
                            
                            StartLoad = Application.WorksheetFunction.VLookup(Cells(r, 5), Sheets("Sheet2").Range("B1:J10"), 5, False)
                            EndLoad = Application.WorksheetFunction.VLookup(Cells(r, 5), Sheets("Sheet2").Range("B1:J10"), 8, False)
                            Ramprate = Application.WorksheetFunction.VLookup(Cells(r, 5), Sheets("Sheet2").Range("B1:J10"), 9, False)
                            Cells(r, 6).Value = StartLoad
                            Cells(r, 7).Value = EndLoad
                            Cells(r, 8).Value = Ramprate
                            
    Errorhandler:
                            If Err.Number = 1004 Then
                            Cells(r, 6).Value = "0"
                            Cells(r, 7).Value = "0"
                            Cells(r, 8).Value = "0"
                            
                            Resume Next
                            End If
                           
                            
                            
                            r = r + 1
                Next MinuteInterval
            Next TimeInterval
        Next StDate
    
        Columns("C:C").NumberFormat = "dd.mmm.yyyy"
        Columns("D:D").NumberFormat = "hh:mm"
        Columns("E:E").NumberFormat = "dd.mmm.yyyy hh:mm"
    
        Application.ScreenUpdating = True
        
    
    
        
        
        
    End Sub
    

    Now the problem is that , on error it does not execute below code

    Errorhandler:
                            If Err.Number = 1004 Then
                            Cells(r, 6).Value = "0"
                            Cells(r, 7).Value = "0"
                            Cells(r, 8).Value = "0"
                            
                            Resume Next
                            End If

    and the other problem is that it is too slow. 

    May you please correct it.

    Sunday, August 28, 2016 10:05 AM
  • What happens if you change

    If Err.Number = 1004 Then

    to

    If Err.Number <> 0 Then

    Also, I'd change the line

    Resume Next

    to

    Err.Clear


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Friday, September 2, 2016 8:29 AM
    • Marked as answer by zubairg Wednesday, September 21, 2016 4:20 AM
    Sunday, August 28, 2016 11:18 AM
  • >>>Now the problem is that , on error it does not execute below code

    According to your description, do you get any change when you follow Hans's suggestion? 

    I suggest that you could display the error message, refer to below code:
    ErrorHandler:
    If Err.Number <> 0 Then
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
    Resume Next

    >>>and the other problem is that it is too slow. 

    In addition could you unload your Excel file on OneDrive, that will help us reproduce and resolve your issue.

    Thanks for your understanding.
    Monday, August 29, 2016 2:19 AM