Runtime error 91 doesn't appear to be consistent

[Huhenyo]

I'm confused why I'm getting a runtime error 91 (object variable or with block variable not set) on just the last two instances (commented to indicate) of my "with" statements. Why would all the others work just fine, but the last two not work correctly? DailyMME and TotalMME are not coming across empty. Any ideas what is different about them that they give this error when all the rest work fine? Any help is greatly appreciated.

Sub FixColumnFormatting(ByVal DispDate, ByVal Qty, ByVal DaysSupply, ByVal Refills, ByVal RxNum, ByVal Chart, _
                        ByVal DailyMME, ByVal TotalMME)
    ' Format Dates
    If IsEmpty(DispDate) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(DispDate + 1))
            Selection.NumberFormat = "mm/dd/yy;@"
            .Value = .Value
        End With
    End If
    If IsEmpty(Qty) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(Qty + 1))
            Selection.NumberFormat = "0"
            .Value = .Value
        End With
    End If
    If IsEmpty(DaysSupply) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(DaysSupply + 1))
            Selection.NumberFormat = "0"
            .Value = .Value
        End With
    End If
    If IsEmpty(Refills) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(Refills + 1))
            Selection.NumberFormat = "0"
            .Value = .Value
        End With
    End If
    
    If IsEmpty(RxNum) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(RxNum + 1))
            Selection.NumberFormat = "0"
            .Value = .Value
        End With
    End If
    
    If IsEmpty(Chart) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(Chart + 1))
            Selection.NumberFormat = "0"
            .Value = .Value
        End With
    End If
    
    If IsEmpty(DailyMME) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(DailyMME + 1))
            Selection.NumberFormat = "0"
            .Value = .Value  ' This line gives a runtime error 91 - object variable or with block variable not set
        End With
    End If
    
    If IsEmpty(TotalMME) = False Then
        With Intersect(ActiveSheet.UsedRange, Columns(TotalMME + 1))
            Selection.NumberFormat = "0"
            .Value = .Value  ' This line gives a runtime error 91 - object variable or with block variable not set
        End With
    End If
End Sub

 

[Huhenyo]

I was thinking that maybe it was the fact that I had the Selection. before each of the second lines of the "With" statements when they aren't needed, but removing them didn't make any difference.

 

[Huhenyo]

I guess another piece of information that may be helpful is that the columns of DailyMME and TotalMME do not have contiguous data whereas the other columns do. I don't know if that matters or not, but thought it might be worth noting.

Thank you,
-Nick

 

[Huhenyo]

Anyone have any ideas on this thread?

 

[Huhenyo]

So, removing the Selction. from the front of the NumberFormat just caused the same runtime error on that line in addition to the .Value = .Value line. One thing I thought of that could have something to do with the issue is that the data in the DailyMME and TotalMME columns is calculated data whereas the data from all the other columns is just a straight extraction from a .txt file. I don't know if that has anything to do with it, but I've tried multiple set commands on the code without luck. This is what I was using before I encountered this error:

    If IsEmpty(DailyMME) = False Then
        Columns(DailyMME + 1).Select
            With Selection
            Selection.NumberFormat = "0"
            .Value = .Value
        End With
    End If
    
    If IsEmpty(TotalMME) = False Then
        Columns(TotalMME + 1).Select
            With Selection
            Selection.NumberFormat = "0"
            .Value = .Value
        End With
    End If

I changed it to improve efficiency, but then ran into the runtime error. I'm sure it is something simple as it always is, but I can't seem to figure it out. Any help would be greatly appreciated.

Thank you