Warm tip: This article is reproduced from stackoverflow.com, please click
excel vba

Concatenate / Combine Columns with time format and general format

发布于 2020-03-27 10:21:10

I'm stuck with a problem and can't find a solution. I want to concatenate cells with two different formats. One cells has the format "tt:mm" and the other cell has format "general" how would I concatenate the cells? I have seen online that this can be done in Exel with something like

=A1 & TEXT(B1,"tt:mm")

or =CONCATENATE(TEXT(A1;"tt:mm"); " ";B1)

But how is this done in VBA via a loop?

I have tried to just add the cells together but I the date is not returned in the format that I want.

Sub  Merge()
Dim i As Long
Dim time As String
LastRow = Range("A" & StartRow).End(xlDown).Row

For i = StartRow To LastRow
Range("B" & i).Value = Range("B" & i).Value & " " & Range("A" & i).Value
Range("B" & i).NumberFormat = "tt:mm General" 
End Sub    

So if I run this code for xyz and 02:30 then I get 0,15625 xyz but I want to get 02:30 xyz.

Many thanks for any help.

Questioner
Andreas Hild
Viewed
117
Warcupine 2019-07-03 22:31

Make this line: Range("B" & i).Value = Range("B" & i).Value & " " & Range("A" & i).Value

Into: Range("B" & i).Value = format(Range("B" & i).Value, "hh:mm") & " " & Range("A" & i).Value

This link has the different format strings: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications