Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    42

    Unanswered: Replace dates in column on multiple sheets

    So I have 5 sheets in this workbook, all having miscellaneous dates in column F starting in the second row. I would like a macro to update/find/replace all dates in column F using date variable 'targetDay' (6/30/17), but I don't want the date added to empty cells in column F.
    The code below successfully builds the new date I want to use, but I've been unsuccessful creating the loop or find/replace process all day.
    Any help would be appreciated!

    Code:
    Sub setDate()
    
        Dim i As Long, dt As Date, firstDay As Date, secondDay As Date, targetDay As Date
    
        dt = Date
        firstDay = DateSerial(Year(dt), 1, 1)
        secondDay = DateAdd("m", 5, firstDay)
        targetDay = DateAdd("d", 29, secondDay)
        
        i = 2
        While Cells(i, 6).Value <> ""
            Range(Cells(i, 6), Cells(i, 6)).Select
            ???
    
            i = i + 1
        Wend
        
    End Sub

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,081
    Provided Answers: 14
    You can't use the value of a cell being "" as the termination reason for the loop. If you do, it will stop running when it hits the first empty cell.

    If you have a column that is always populated, you can use it to define the range that you need to check:
    Code:
    Sub setDate()
    Dim lngCells As Long
    Dim lngCell As Long
    Dim datWorking As Date
    Dim rngDates As Range
    
    datWorking = Date
    datWorking =  DateSerial(Year(datWorking), 1, 1)
    datWorking = DateAdd("m", 5, datWorking)
    datWorking = DateAdd("d", 29, datWorking)
    
    'Assume that column A is always populated and row 1 contains headers.
    lngCells = WorksheetFunction.Counta(ActiveSheet.Columns(1)) - 1
    Set rngDates = ActiveSheet.Range(Cells(2, 6), Cells(lngCells, 6))
    
    For lngCell = 2 to lngCells
    
    If rngDates.Cells(lngCell).Value <> "" Then rngDates.Cells(lngCell).Value = datWorking
    
    Next lngCell
    
    End Sub
    Have a play and see if it helps.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Tags for this Thread

Posting Permissions

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