Insert Semi Colon

[billandrew]

Good evening all

I am adding a semi colon in string of 3 after the 1st number & a semi colon in string of 4 after the 2nd number utilizing the below code. The issue I am having is at the end of the range when additional values are entered the cell returns 0:00. when the code is run again the value entered is correct with the semi colon and cell formatting.

Thanks for any assistance


Sub InsertSemiColonw()

Dim lr As Long
Dim i As Long

lr = Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To lr

If Len(Cells(i, "B")) = 3 Then

Cells(i, "B") = Left(Cells(i, "B"), 1) & ":" & Right(Cells(i, "B"), 1)

ElseIf Len(Cells(i, "B")) = 4 Then

Cells(i, "B") = Left(Cells(i, "B"), 2) & ":" & Right(Cells(i, "B"), 2)


End If

Next

End Sub

 

[Rick Rothstein]

I agree, this is most likely the cause. But then, why is it showing up as "0.00"? The best I could replicate is "0:00", which is according to my Regional Settings in the OS. If inserting a colon works to format time, why would it show up as "0.00"?

I misspoke when I said the 3/4 digit number entered into the cell is hours... it is not hours, it is days offset from "date zero". Dates in Excel (or VBA for that matter) are floating point numbers... the whole number is the days offset from date zero (1 is January 1, 1900) and the decimal value is the fraction of a 24-hour day expressed as a time value. When there is no decimal part to the floating point number, it means the fraction is 0, to the time value is midnight which Excel displays as 0:00. So when you enter a whole number into a cell that is formatted as a Time value (whether directly formatted that way or whether the format is extended from the cell above, the time portion of the date is displayed... 0:00 which represents midnight... the whole number remains in the cell, but since the Time format does not display the month/day/year it represents, you only see the time part which, as stated above, is 0:00.

 

[billandrew]

actually missed it. This works perfect.

Awesome

THANK YOU!

 

[iliace]

actually missed it. This works perfect.

Awesome

THANK YOU!

Cool! Glad to help.

I misspoke when I said the 3/4 digit number entered into the cell is hours... it is not hours, it is days offset from "date zero". Dates in Excel (or VBA for that matter) are floating point numbers... the whole number is the days offset from date zero (1 is January 1, 1900) and the decimal value is the fraction of a 24-hour day expressed as a time value. When there is no decimal part to the floating point number, it means the fraction is 0, to the time value is midnight which Excel displays as 0:00. So when you enter a whole number into a cell that is formatted as a Time value (whether directly formatted that way or whether the format is extended from the cell above, the time portion of the date is displayed... 0:00 which represents midnight... the whole number remains in the cell, but since the Time format does not display the month/day/year it represents, you only see the time part which, as stated above, is 0:00.

We're on the same page then. Based on OP's feedback, it sounds like this was the issue. You can either turn off the automatic formatting, as you originally suggested, but if you don't want to do that (or you need to distribute the worksheet to other users), then formatting all the empty cells as a regular number is a plausible workaround.

I was just confused about the formatting, 0.00 vs 0:00. The former indicates to me that this is a number format - in which case there should be a fractional portion, based on what you said. The latter looks like a time format, which would exhibit the issue of something like 1234 being interpreted as a number of days, and therefore not show the fractional time portion. When I attempted to recreate the issue, I could only get the 0:00 result (again, based on my OS settings).