I am trying to return the time value as a string, with no ":" or "AM/PM". I do not want the value returned as a decimal.
If it is currently 7:53 PM, I want "195300".
A little bit about the process.
In a formula in cell D6, I pull data from the Excel sheet and format it into an ID.
It became necessary for the users to copy and paste this ID.
They couldn't copy the value in D6 without getting the formula (not interested right now if there's a better solution for that specifically), so I run a macro upon a button press.
The macro checks if the cells that contribute to the formula to make the ID are not empty.
If they are not empty, I put the value in D6 into K99 for the user to copy.
It is in this section of code I want to take the current time, and format it as "HHMMSS" with no ":" or "AM/PM".
I want to store it in a random cell for now, V128, and I'll add this to my ID later.
Private Sub Worksheet_Change(ByVal mnTarget As Range)
'other code that isn't relevent here
For Each Cell In Range("G18,E27,E28, E40, E42")
If Cell.Value <> "" Then
Range("K99").Value = Range("D6").Value
Else
Range("K99").Value = ""
End If
Next Cell
'test point to show the current time
'MsgBox Time
Dim var As String
var = Time
tmp = Replace(var, ":", "")
Range("V128").Value = var
End Sub
This returns time formatted with colons and AM/PM.
I tried a formula in the worksheet.
If "7:53:00 PM" is in cell V128, in a nearby cell I tried =SUBSTITUTE(V128,":","").
This returned a nonsensible value.
Range("V128").Value = Format(Time, "hhmmss")
should do what you want.