VBA to Concatenate and Autofill??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!
 
Assuming you have labels in row 1:

Sub MakeDates()
Range("D1").VAlue = "Date"
Range("D2:D" & Range("C65536").End(xlUp).Row).FormulaR1C1 = _
"=DATE(RC[-1],RC[-3],RC[-2])"
With Range("D:D")
.NumberFormat = "mm/dd/yy"
.EntireColumn.AutoFit
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

HTH,
Bernie
MS Excel MVP


JCarter said:
Sorry I didn't specify. I would like a date format, ie. mm/dd/yy
Click to expand...
 
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!!
Click to expand...

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
 
Back
Top