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.
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