Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro date problem (2003)

    I have the following macro that fills in the missing spaces. When there is a date to fill down, it puts in the numeric date like 23456. Is there a way to adjust this macro to fill in what is there 3/1/08 etc.

    Sub FillBlanks()
    Dim rng As Range
    On Error Resume Next
    Set rng = ActiveSheet.UsedRange.Columns("U:AK")
    With rng
    .NumberFormat = "General"
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Copy
    .PasteSpecial xlPasteValues
    End With
    Set rng = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro date problem (2003)

    I'd remove the line

    .NumberFormat = "General"

    This line removes the date format, which is obviously not what you want.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro date problem (2003)

    <P ID="edit" class=small>(Edited by wdwells on 29-Apr-08 12:25. Code replaced (Original version was inconsistent).)</P>Try the following: <pre>Sub FillBlanks()
    Dim rng As Range
    Dim blank As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rng = ActiveSheet.UsedRange.Columns("U:AK")
    With rng
    For Each blank In rng.Cells
    If blank.Formula = "" Then
    With blank
    .FormulaR1C1 = "=R[-1]C"
    .Offset(-1, 0).Copy
    .PasteSpecial xlPasteFormats
    End With
    End If
    Next blank
    End With
    Set rng = Nothing
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Regards
    Don

Posting Permissions

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