+ Reply to Thread
Results 1 to 3 of 3

Macro to insert copy of row if Open dates are less than Closed dates

Hybrid View

bongielondy Macro to insert copy of row... 11-05-2019, 03:14 AM
AlphaFrog Re: Macro to insert copy of... 11-05-2019, 07:05 AM
bongielondy Re: Macro to insert copy of... 11-14-2019, 02:50 AM
  1. #1
    Registered User
    Join Date
    03-28-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 365
    Posts
    79

    Macro to insert copy of row if Open dates are less than Closed dates

    1. if value in column I is 'Maintanance', (n.b. we will call row the 'Maintanance row) then check if the 'Ticket Open' dates for the next rows are less than 'Ticket Closed' for the 'Maintanance' row
    2. Insert a copy of the 'Maintanance row after the last row whose 'Ticket Open' is less than the 'Ticket Closed' for the 'Maintanance' row
    3. 'Ticket Open' and 'Ticket Closed' for the inserted row should be the same as the 'Ticket Closed' for the 'Maintanance' row
    4. then look for the next Maintanance row in column I and repeat same procedure


    I have attached the file with row I had to insert manually after a macro I created failed. The original file has more than 500 Maintanance rows.

    The Maintanance row is in row 6. I inserted a copy of the 'Maintanance' row in row 10.


    Please assist. I have been working on this file since last week.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Macro to insert copy of row if Open dates are less than Closed dates

    Sub Maintenance()
        Dim rngFound As Range, FirstFound As String
        Dim tc As Date, i As Long, counter As Long
        
        Application.ScreenUpdating = False
        
        Set rngFound = Columns("I").Find("Maintenance", [I1], xlValues, xlWhole, 1, 1, 0)
        
        If Not rngFound Is Nothing Then
            FirstFound = rngFound.Address
            Do
                tc = rngFound.Offset(, 3).Value 'Ticket closed
                For i = rngFound.Row + 1 To Range("I" & Rows.Count).End(xlUp).Row + 1
                    If Range("K" & i).Value >= tc Or Range("K" & i).Value = Empty Then
                        rngFound.EntireRow.Copy
                        Rows(i).Insert
                        Range("I" & i).Resize(, 3).Value = Array("Out of Maintenance", "", tc)
                        counter = counter + 1
                        Set rngFound = Range("I" & i)
                        Exit For
                    End If
                Next i
                Set rngFound = Columns("I").FindNext(After:=rngFound)
            Loop Until rngFound.Address = FirstFound
        End If
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        MsgBox counter & " rows inserted.", vbInformation, "Maintenance Tickets"
        
    End Sub
    Last edited by AlphaFrog; 11-05-2019 at 07:20 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    03-28-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 365
    Posts
    79

    Re: Macro to insert copy of row if Open dates are less than Closed dates

    Hi.

    This is exactly what I wanted. Sorry for the late response. I haven't been well and was offline. Thank you so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-08-2016, 08:51 PM
  2. Insert missing dates into a list of dates using vba
    By Declamatory in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2015, 12:18 PM
  3. [SOLVED] Macro that can grab dates within a parameterized list and insert rows with those dates
    By UMBiii in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-12-2014, 09:14 AM
  4. Open Workbook if closed, keep open if open, insert password
    By jwahl16 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2013, 04:09 PM
  5. Macro to open a closed workbook and select copy cells into an open workbook
    By helloganesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-10-2013, 02:00 PM
  6. Need a formula to calculate time between open and closed dates and times
    By DebbieF in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-03-2012, 02:45 AM
  7. Replies: 7
    Last Post: 03-27-2011, 09:03 AM

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