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

 

[iliace]

In the future, please use [ code ] tags so that your code listing is easier to read. It is the # button on the right of the toolbar. Like so:

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

As for your question, can you please post an example of what your data looks like? Specifically:
* Is your intention to create a time value in each of the cells in column B? e.g. 1234 becomes 12:34pm, etc?
* Can you post an example of your data?
* Where are the additional values entered - at the bottom of Column B?
* Which cell returns 0:00, and under what circumstances? Is there a Worksheet_Change event, or something along those lines?

 

[billandrew]

- My intention is to create a time value in each of the cells in Column B
- example below
- yes, the additional values would be entered at the bottom of Column B
- When the user enters additional data at the bottom of existing data, i.e. 1015 the view (formatting) is 0.00. When the code is run, the correct value (view) is visualized. now becomes or viewed as 10:15.
- There is no Worksheet_Change event.


Hope this explanation is clear.

Thank You

806	8:06
1015	10:15
Following Initial call of code - User enters additional data
1025
In cell the above looks (formatted) 0.00
Code is rerun - (Below) The cell value now is correct
10:25

<tbody>
</tbody>

 

[iliace]

- When the user enters additional data at the bottom of existing data, i.e. 1015 the view (formatting) is 0.00. When the code is run, the correct value (view) is visualized. now becomes or viewed as 10:15.

Hmm... the only thing I can think of is number format. Try this code instead:

Sub InsertSemiColonw()

  Dim lr As Long
  Dim i As Long
  
  lr = Range("B" & Rows.Count).End(xlUp).Row
  
  For i = 2 To lr
    With Cells(i, "B")
      If .Value >= 100 And .Value < 9999 Then
        .Value = Left(.Value, Len(.Value) - 2) & _
                ":" & Right(.Value, 2)
        .NumberFormat = "h:mm"
        .Offset(1, 0).NumberFormat = "0"
      End If
    End With
  Next
  
End Sub

 

[billandrew]

iliace

Thank You for the response. This code does work for the cells which have data, however interestingly the offset formats the row below the existing data with the Number Format as intended, however (another however lol..) rows below the offset with no data are again returning 0.00 when a value is entered.


Perplexed..

 

[iliace]

Maybe do it this way then... grasping at straws at this point. I'm almost certain it has to do with number formats.

Sub InsertSemiColonw()

  Dim lr As Long
  Dim i As Long
  
  lr = Range("B" & Rows.Count).End(xlUp).Row
  
  For i = 2 To lr
    With Cells(i, "B")
      If .Value >= 100 And .Value <= 9999 Then
        .Value = Left(.Value, Len(.Value) - 2) & _
                ":" & Right(.Value, 2)
        .NumberFormat = "h:mm"
      End If
    End With
  Next
  
  Range(Cells(i, "B"), Cells(i, "B").End(xlDown)).NumberFormat = "0"
End Sub

In other words, format all cells below last non-blank cell.

 

[Rick Rothstein]

- When the user enters additional data at the bottom of existing data, i.e. 1015 the view (formatting) is 0.00. When the code is run, the correct value (view) is visualized. now becomes or viewed as 10:15.

The reason it is doing that is because of a defaulted Excel Options setting which extends the formatting from the cell above... since your code custom formats the cells it processes as time values, new entries are inheriting that custom format, however, since new numbers added below the last number processed by your code will not have a colon in them, they are interpreted as hours, but those "hours" being 3 or 4 digits long are greater than 23 which means they are too big for h:mm format to display as a time value and so are "zeroed out" in the time display. You can turn this automatic extension of your custom format as follows, but note that it will apply to all sheets in all workbooks from here on out. Bring up the Excel Options dialog box, click the "Advanced" option in the left-hand list, look at the "Editing options" section (should be the first section at the top of the right-hand list) and remove the check mark from the option labeled "Extend data range formats and formulas", then click the OK button. That should stop the "zeroing out" of new entries.

 

[iliace]

Rick to the rescue!

The reason it is doing that is because of a defaulted Excel Options setting which extends the formatting from the cell above... since your code custom formats the cells it processes as time values, new entries are inheriting that custom format, however, since new numbers added below the last number processed by your code will not have a colon in them, they are interpreted as hours, but those "hours" being 3 or 4 digits long are greater than 23 which means they are too big for h:mm format to display as a time value and so are "zeroed out" in the time display. You can turn this automatic extension of your custom format as follows, but note that it will apply to all sheets in all workbooks from here on out. Bring up the Excel Options dialog box, click the "Advanced" option in the left-hand list, look at the "Editing options" section (should be the first section at the top of the right-hand list) and remove the check mark from the option labeled "Extend data range formats and formulas", then click the OK button. That should stop the "zeroing out" of new entries.

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"?

 

[billandrew]

Thanks Mr. Rothstein

I'd most likely would not prefer to amend the automatic extension.

Maybe I or someone could find an alternative.

Thank You

 

[iliace]

Did you try my most recent code suggestion?