Results 1 to 9 of 9

Counting days until threshold reached

Threaded View

  1. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,815

    Re: Counting days until threshold reached

    Sub get_dates()
    
    
    Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long, r As Long, c As Long, n1 As Long, n2 As Long
    Dim lastrow As Long, Lastcol As Long
    Dim inarr, outarr
    
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    
    'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      'Application.Calculation = xlCalculationManual
    
    Set ws1 = Worksheets("Sheet1")
    ws1.Activate
    
    With ws1
    
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
        Lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
        
        inarr = .Range(.Cells(2, 1), .Cells(lastrow, Lastcol))
        
        ReDim outarr(1 To UBound(inarr, 1), 1 To 3)
        
        Crop = inarr(1, 1)
        c = 1
        
        For l = 200 To 400 Step 100
            
            For r = 1 To UBound(inarr, 1)
            
                If inarr(r, 1) <> Crop Then Crop = inarr(r, 1)
                   tsum = 0
                   j = r
                   Do While tsum < l
                     If inarr(j, 1) <> Crop Then GoTo nextr
                      tsum = tsum + inarr(j, 4)
                      j = j + 1
                      If j > UBound(inarr, 1) Then
                        If tsum >= l Then outarr(r, c) = WorksheetFunction.Max(inarr(j - 1, 2) - inarr(r, 2) + 1, 0)
                        GoTo nextr
                      End If
                   Loop
                   outarr(r, c) = WorksheetFunction.Max(inarr(j - 1, 2) - inarr(r, 2) + 1, 0)
    nextr:
            Next r
         c = c + 1
        Next l
    
    End With
    
    ws1.Range("E2").Resize(UBound(outarr, 1), UBound(outarr, 2)) = outarr
    
    'Reset Macro Optimization Settings
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 12-12-2018, 12:34 AM
  2. Counting Times Threshold is Reached
    By Casval in forum Excel General
    Replies: 4
    Last Post: 02-04-2018, 05:37 AM
  3. Replies: 1
    Last Post: 11-30-2016, 08:02 PM
  4. [SOLVED] Move specific cells to a new sheet when a date is reached or 30 days from being reached
    By Albert Dirk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-26-2016, 04:44 AM
  5. [SOLVED] Need to formulate trigger once a certain value or milestone or threshold has been reached
    By KaziProttoy in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-09-2015, 11:03 AM
  6. [SOLVED] Find first cell where threshold value is reached, return value from adjacent column
    By CatSqueezer in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-06-2013, 08:49 PM
  7. [SOLVED] Counting days till value is reached
    By Montoro22 in forum Excel General
    Replies: 11
    Last Post: 07-06-2012, 11:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1