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.
Code:
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