Date

[melvinm]

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

 

[Greg Truby]

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]

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]

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]

Then definitely listen to [first half of reply by] Greg, not me! That's an excellent solution.

 

[Gates Is Antichrist]

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.

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.