CODEWITHSUNDEEP
excelvba
user2671251
user2671251

Reputation: 23

Need the macro to run ok even when the selection includes cells that are empty or have text

The macro when run on a selection takes a datetime shown as 20130808191057 and formats it as 19:10 IT.

The macro works great but if i were to select a range of cells which includes empty cell it would stop and get an error. how can I make it ignore empty cells and cells wth text. I would like to run it on a selected column if possible. 

Sub TimeCreator()
    Dim r As Range, t As String
    For Each r In Selection
        t = r.Text
        r.Clear
        r.Value = TimeSerial(Mid(t, 9, 2), Mid(t, 11, 2), Right(t, 2))
        r.NumberFormat = "[h]:mm  ""IT"""
    Next
End Sub

Thanks in advance.

Upvotes: 0

Views: 68

Answers (1)

Cor_Blimey
Cor_Blimey

Reputation: 3310

As Andy G states - you need to run a check if the cell is empty and skip if so.

You can do it like:

Sub TimeCreator()
    Dim r As Range, t As String
    For Each r In Selection
        If r.Value <> Empty Then 'test if the cell is blank (note, a zero length string is not blank in Excel)
            t = r.Text
            'r.Clear is unnecessary as you assign to r.Value
            r.Value = TimeSerial(Mid(t, 9, 2), Mid(t, 11, 2), Right(t, 2))
            r.NumberFormat = "[h]:mm  ""IT"""
        End If
    Next
End Sub

However, it would be better to only run the macro on cells that have values in the first place, which you can do using Range.SpecialCells(), like:

Sub TimeCreator()
    Dim r As Range, rangeToRunOn As Range, t As String
    Set rangeToRunOn = Selection.SpecialCells( _
                           xlCellTypeConstants, _
                           xlTextValues OR xlNumbers)
    If Not rangeToRunOn is Nothing Then
        For Each r In rangeToRunOn
            t = r.Text
            'r.Clear is unnecessary as you assign to r.Value
            r.Value = TimeSerial(Mid(t, 9, 2), Mid(t, 11, 2), Right(t, 2))
            r.NumberFormat = "[h]:mm  ""IT"""
        Next
    End If
End Sub

Upvotes: 1

Related Questions