I am trying to return the time value as a string, with no ":" or "AM/PM" in it. I do not want the value returned as a decimal. So if it is currently 7:53 PM, I want the code to return "195300".
A little bit about the code, in a formula in cell D6, I pull data from the Excel sheet and format it into an ID.
It later became necessary for the users to copy and paste this ID. But, 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 still returns time formatted with colons and AM/PM. Please help, I can't find anything on the internet for this solution.
I've also tried using formulas in the worksheet, so if "7:53:00 PM" is in cell V128, in a nearby cell I tried =SUBSTITUTE(V128,":","") but this returned a nonsensible value.
Range("V128").Value = Format(Time, "hhmmss")
should do what you want.