On Fri, 23 Sep 2005 09:17:14 -0700, "JCarter"
wrote:

I have a issue where 3 separate columns (A - C) have pieces of dates in them.
Month (A), Day (B), Year (C).

What I need to happen is to Concatenate A-C, then Auto fill down as far as
necessary, and then go back and Copy Paste As Value to remove the formula.

Can anyone provide coding guidance on this? I would greatly appreciate it!!

This needs to be done in VB. And, since there's no need to put a formula in
the cell, there's no need to Copy/Paste as Value.

Here's one method -- very basic code.

It writes the result into column D.

The only checking is to ensure there are three numbers in columns A, B, and C.
You could add a sanity check to that. The way Excel works, a date like
13-34-2005 would come out as 3 Feb 2006. You may or may not want this.

=================
Sub ConcatDates()
Dim Tbl As Range
Dim Temp(1 To 3)
Dim rw As Range, c As Range
Dim i As Long

Set Tbl = [A2:D1000]
Tbl.Columns(4).Clear

For Each rw In Tbl.Rows
For i = 0 To 2
Temp(i + 1) = Cells(rw.Row, rw.Column + i).Value
Next i
If Application.WorksheetFunction.Count(Temp) = 3 Then
Cells(rw.Row, 4).Value = DateSerial(Temp(3), Temp(1), Temp(2))
Else: Tbl(rw.Row, 4) = ""
End If
Next rw

End Sub
==================


--ron