Date

melvinm

Board Regular
Joined
Oct 27, 2005
Messages
152
HI eveyone
I have a list of dates in column A. lets say i have in a1 i have 23/12/05 and then in a8 i have 24/12/05. The problem is in all the inbetween cells that is from a2 to a7 it should be 23/12/05.

Is there way to automatically input rather than putting the date manually in each cell.


Thanks for any help
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Assuming you have tons of these, else why post...
  1. Highlight A1:Axxxx (however many rows you need to do this to).
  2. From the menu: Edit | Goto... | Special (button) | Blanks (option button)
  3. Type the equals sign (=) and then hit your up arrow.
  4. Hold down your Ctrl key when you hit the Enter key, this will put the formula in all cells at once.
[Optional]
  1. Highlight all cells in A1:Axxxx.
  2. Copy (Ctrl+C or Edit | Copy).
  3. From the menu Edit | Paste Values... | Values (option button).
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
That's so simple, that I and all must figure that there's more of a problem than the question indicates.
You can set cell A2 as
=$A1
and copy this cell for all of column 2 through column 7.

In fact, for this example you can leave off the dollar sign ($) and it would still work.

Now, is the real "hard" question coming? :wink:
 

melvinm

Board Regular
Joined
Oct 27, 2005
Messages
152
hi,

I have around 10,000 lines and its not equidistance meaning the number of cells between two dates is not unifrom.



Thanks for idea. will try it out soon



cheers
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Try this. Remove each line that contains "debug" unless you find them to be useful, or comment or delete them after they've helped you understand the code.
Code:
Sub myPropagate()
    Dim dHoldDate As Date, r As Range, col As Object
    
    For Each col In ActiveSheet.UsedRange.Columns
        Debug.Print "col "; col.Column
        dHoldDate = col.Cells(1, 1).Value
        Debug.Print "column top "; dHoldDate
        For Each r In col.Cells
            If r.Value = "" Then
                Debug.Print "cell val before <" & r.Value & ">"
                If dHoldDate <> 0 Then r.Value = dHoldDate
                Debug.Print "cell val after <" & r.Value & ">"
            Else
                dHoldDate = r.Value
                Debug.Print "  new propagation base " & dHoldDate
            End If
        Next r
    Next col
End Sub
If it matters, you could tweak performance by changing "If dHoldDate <> 0" to instead test a boolean "bFirstFound" or such.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,317
Messages
5,571,522
Members
412,399
Latest member
Amoldavskii
Top