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

    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!

    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
    End Sub

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    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:
    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