VBA merge date and time and transfer to existing date column

Question

I have a VBA macro I wrote to fix the date in a sheet so it combines the date and time. Date is in B and is not in the format I want which is dd/mm/yyyy and time is in D and is right, hh:mm

The idea is to combine date and time and eventually end up with the correct result filling column B, overwriting what is now there. I have code working which does the combination of the date and time, as well as then selecting the cell and copying the result down to the last row of data, then copying all of that column and pasting values only into column B

Here is what I have

Sub Combine_date_and_time()

    Dim ws As Worksheet
    Set ws = Worksheets("predictology")

    ws.Range("G2") = Application.WorksheetFunction.Text(ws.Range("B2"), "dd/mm/yyyy") & " " & Application.WorksheetFunction.Text(ws.Range("D2"), "hh:mm")
    Range("G2").Select
    Application.Run "PERSONAL.XLSB!CopyFormulaDownToLastRowOfData"
    ws.Range("G:G").Cut
    ws.Range("B2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Two things don't work in this; firstly the copy down ends up copying the same data down, rather it calculating each cell as it would normally and I get a Runtime error 1004 Method 'PasteSpecial'of Object 'Range' failed on the paste line.

I am relatively new to writing VBA and have been browsing a number of posts and taking the relevant pieces I needed, but obviously something/s awry in my compilation of the code

Any help gladly accepted

It adds a date and time, and a datetime value for it appears. However, you should be able to explain what your original data is like. You need to know whether it is text or actual datetime data. — Dy.Lee, Jul 28 '20 at 06:28

Deeraj · Answer 1 · 2020-07-28T10:46:05.457

1

ws.Range("G:G").Cut- This is the error you cannot paste the entire G column in B2 cell you can just change it with G2 and paste and do it with looping if you have to fill down and you can not use cut(only copy can be used) with paste special.you have to remove the data from G if once work is done

edited Jul 28 '20 at 10:46

answered Jul 28 '20 at 10:37

Deeraj

11
4

score 1 · Accepted Answer · answered Jul 28 '20 at 11:11

Your main issue is that you are trying to paste an entire column of data, but starting on the second row. This means that the Column will overflow the bottom - a quick fix would be to use ws.Range("B1").PasteSpecial Paste:=xlPasteValues, but that's not the only issue.

Most importantly, you're putting in a value, not a formula. (Fully qualified, you're changing the default property ws.Range("G2").Value, not the ws.Range("G2").Formula property) This means that every cell will have the same value, as though it was in row 2.

Now, Dates and Times are stored as Numbers in Excel - a Date is an integer number of days, and a time is a decimal portion of a day. This means we can add a date and a time, to get a datetime.

So, the formula we want in cell G2 will probably look something like this:

=TEXT(INT(B2)+MOD(D2,1), "dd/mm/yyyy hh:mm")

(The INT is so that we ignore any Time already in cell B2, and the MOD is so that we ignore any days already in cell D2)

However! Rather than trying to force this to be text, we can also use NumberFormat to display it in the format we want. This makes our formula shorter, and lets you do things like Sort the rows based on the date:

=INT(B2)+MOD(D2,1)

Finally, instead of using Cut and PasteSpecial, we can just use Range.Value=Range.Value. This is very useful, for many of the same reasons that you should try to Avoid using Select in Excel VBA

Sub Combine_date_and_time()

    Dim ws As Worksheet
    Set ws = Worksheets("predictology")

    ws.Range("G2").Formula = "=INT(B2)+MOD(D2,1)"

    Range("G2").Select
    Application.Run "PERSONAL.XLSB!CopyFormulaDownToLastRowOfData"

    With ws.Range(ws.Cells(1,1),ws.UsedRange) 'Adjust for empty rows/columns
        .Columns("B").Value = .Columns("G").Value
        .Columns("B").NumberFormat = "dd/mm/yyyy hh:mm;@" 'Display in your format
        .Columns("G").Clear
    End With
End Sub

For neatness, I would like to replace Range("G2").Select and Application.Run "PERSONAL.XLSB!CopyFormulaDownToLastRowOfData", but I do not know the details of that Subroutine. Theoretically, using the auto-filldown features, you may be able to simplify to this:

Sub Combine_date_and_time()

    Dim ws As Worksheet
    Set ws = Worksheets("predictology")

    With ws.Range(ws.Cells(1,1), ws.UsedRange) 'Adjust for empty rows/columns
        'Column G is Column 7
        .Range(.Range("G2"), .Cells(.Rows.Count, 7)).Formula = "=INT(B2)+MOD(D2,1)"
        .Columns("G").Calculate 'Make sure the values have calculated

        .Columns("B").Value = .Columns("G").Value
        .Columns("B").NumberFormat = "dd/mm/yyyy hh:mm;@" 'Display in your format
        .Columns("G").Clear
    End With
End Sub

cheers @Chronocidal. I can see I was going about it wrong. It is also terrific to have the steps explained so I can see what each of the parameters actually does. May I ask what the 1 does in this `MOD(D2,1)`? By the way, this last one works perfectly. Thank you very much indeed. — honkin, Jul 29 '20 at 01:19
@honkin The `MOD(D2, 1)` will convert a DateTime (e.g. "19/07/2020 12:34") into just a time ("12:34"), so that you don't add a date to a date (e.g. "19/07/2020" + "19/07/2020 12:34" = "06/02/2141 12:34") - if you remember doing the whole "A divided by B is C, remainder D" in primary school, then `MOD(A, B)=D` — Chronocidal, Jul 29 '20 at 07:20

VBA merge date and time and transfer to existing date column

2 Answers2