Concatenate Date and Keep Formatting

[sharc316]

Hi,

I have code below to concatenate two cells in a third cell. The code removes slashes from the destination cell as well. One of the cells has a date and I would like the format of the date to be mmddyy in the concatenated cell.

I've formatted the date as mmddyy in its original cell but when it's concatenated in the destination cell, the zeros get removed.

This is what I would like to get: 030517
This is what I get in the destination cell: 3517

Any help would be appreciated.



Sheets("Sheet1").Range("E1").Value = Sheets("Sheet1").Range("C1").Value & " " & Sheets("Sheet1").Range("D1").Value
'Removes slashes from cell
Worksheets("sheet1").Range("E1").Replace _
What:="/", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True

[rlv]

You can do it in code


Sub Test()
Sheets("Sheet1").Range("E1") = CellMerge(Sheets("Sheet1").Range("C1"), Sheets("Sheet1").Range("D1"))
End Sub


Function CellMerge(DateCell As Range, OtherCell As Range) As String
CellMerge = Format(DateCell.Value, "mmddyy") & " " & OtherCell.Value
End Function


But a cell formula might be simpler:

=TEXT(C1,"mmddyy") & " " & D1

[sharc316]

Thank you rlv. How can I have a macro insert the cell formula into a certain cell on a certain sheet? Into L1 on sheet1 for example?

[rlv]

'Add formula to cell in column L for the first 3 rows
Sub Test()
Dim I As Long
For I = 1 To 3
AddFormulaToCellInColumnL I
Next I
End Sub


Sub AddFormulaToCellInColumnL(RowNum As Long)
Dim MyCell As Range, DateCell As Range, OtherCell As Range
With ThisWorkbook.Worksheets("Sheet1").Rows(RowNum)
Set MyCell = .Range("L1")
Set DateCell = .Range("C1")
Set OtherCell = .Range("D1")
End With


'=TEXT(C1,"mmddyy") & " " & D1
MyCell.Formula = "=TEXT(" & DateCell.Address & "," & DQ("mmddyy") & ") & " & DQ(" ") & " & " & OtherCell.Address
End Sub


'Double quote
Function DQ(AnyString As String) As String
DQ = """" & AnyString & """"
End Function

[sharc316]

Thank you rlv for providing a solution. For some reason this does not work for me. I've copied the code above and pasted into a module. Placed date into cell C1 and Text into cell D1. When I run the code nothing happens. No errors and nothing in cell L1. Please let me know if I'm doing something wrong.

[rlv]

My example makes assumptions that may not match up with your actual workbook. Please post (attach) your workbook here so I can look at it.

[sharc316]

18821

I've attached a test file. It's actually working if the module is on the test file workbook. But when I run it from a personal project that's when it's not working. Also, I would like to include this code as part of a another sub. Any suggestions on doing this? Or maybe just call this macro at the end of that sub?

Thank you very much for your help and time.

[rlv]

The earlier example assumed the code was going to work on "sheet1" in the same workbook. If you want to run it from somewhere else, you have to tell it what sheet you want it to work on. There are a couple of ways to do that. This example will always work on the active sheet


Sub AddFormulaToCellInColumnL(RowNum As Long) Dim MyCell As Range, DateCell As Range, OtherCell As Range

With ActiveSheet.Rows(RowNum)
Set MyCell = .Range("L1")
Set DateCell = .Range("C1")
Set OtherCell = .Range("D1")
End With

'=TEXT(C1,"mmddyy") & " " & D1
MyCell.Formula = "=TEXT(" & DateCell.Address & "," & DQ("mmddyy") & ") & " & DQ(" ") & " & " & OtherCell.Address
End Sub

This sub is just an illustrative example. I don't see any reason why you could not include similar code in another of your subs.

[sharc316]

Thank you rlv. It's working now. Appreciate your help!