Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Concatenate and Autofill??
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!! -- JCarter Still Learning |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Concatenate and Autofill??
Concatenate A-C into what format?
Tim -- Tim Williams Palo Alto, CA "JCarter" wrote in message ... 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!! -- JCarter Still Learning |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Concatenate and Autofill??
Sorry I didn't specify. I would like a date format, ie. mm/dd/yy
-- JCarter Still Learning "Tim Williams" wrote: Concatenate A-C into what format? Tim -- Tim Williams Palo Alto, CA "JCarter" wrote in message ... 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!! -- JCarter Still Learning |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Concatenate and Autofill??
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" wrote in message ... Sorry I didn't specify. I would like a date format, ie. mm/dd/yy -- JCarter Still Learning "Tim Williams" wrote: Concatenate A-C into what format? Tim -- Tim Williams Palo Alto, CA "JCarter" wrote in message ... 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!! -- JCarter Still Learning |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to Concatenate and Autofill??
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming | |||
Autofill help | Excel Programming |