Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Want to highlight missing dates

    Hello All,

    I have a code that insert the missing dates in column A . It perfectly works i need that it works for all the sheets in workbook and highlight the missing dates which is inserted via code ...


    Code:
    Public Sub ProcessData()
    Const TEST_COLUMN As String = "A"    '<=== change to suit
    Dim i As Long
    Dim LastRow As Long
    Dim tmp As Long
    
        With ActiveSheet
            
            LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
            For i = LastRow To 2 Step -1
            
                If .Cells(i, "A").Value <> .Cells(i + 1, "A").Value And _
                    .Cells(i, "A").Value < .Cells(i + 1, "A").Value - 1 Then
                    
                    tmp = .Cells(i + 1, "A").Value
                    .Rows(i + 1).Resize(tmp - .Cells(i, "A").Value - 1).Insert
                    .Cells(i, "A").AutoFill .Cells(i, "A").Resize(tmp - .Cells(i, "A").Value)
                    .Cells(i + 1, "L").Resize(tmp - .Cells(i, "A").Value - 1, 4).Value = 0
                End If
            Next i
            
        End With
        
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Farrukh,

    The following should do the trick. Note this is AIR code, i.e. untested!
    Code:
    Public Sub ProcessData()
    
      Const TEST_COLUMN As String = "A"    '<=== change to suit
      Dim i As Long
      Dim LastRow As Long
      Dim tmp As Long
      Dim wks as Worksheet
    
      For Each wks in Activeworkbook.Sheets
    
        With wks
            
            LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
            For i = LastRow To 2 Step -1
            
                If .Cells(i, "A").Value <> .Cells(i + 1, "A").Value And _
                    .Cells(i, "A").Value < .Cells(i + 1, "A").Value - 1 Then
                    
                    tmp = .Cells(i + 1, "A").Value
                    .Rows(i + 1).Resize(tmp - .Cells(i, "A").Value - 1).Insert
                    .Cells(i, "A").AutoFill .Cells(i, "A").Resize(tmp - .Cells(i, "A").Value)
                    .Cells(i + 1, "L").Resize(tmp - .Cells(i, "A").Value - 1, 4).Value = 0
                End If
            Next i
            
        End With
    
      Next wks
        
    End Sub
    Last edited by RetiredGeek; 2012-10-01 at 08:26.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Sir i find an error.

    Run time 438 (object doesn't support this property or method).


    Thanks

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Farrukh,

    Sorry, it should have been: For Each wks In ActiveWorkbook.Sheets

    I've fixed it in the code above also.

    That's the problem with not testing at my age you forget things.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Sir RetiredGeek,

    wonderful i salute your help and support it is really admirable.

    Thank you

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    You're welcome.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

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