VBA Error 91: Object variable or With block variable not set

[unknownymous]

Hi Guys,

I am having an VBA Error 91 using below codes and it stopped in " If none_lastrow.Row > na_lastrow.Row Then"

Thanks in advance for the help.

===========================

Sub NONE()



'Set conditional formatting
Set None_check_tab = Workbooks("Data Macro").Sheets("NONE Check")

Set Col = None_check_tab.Columns(2)
Col.FormatConditions.Add Type:=xlTextString, String:="_NONE", _
TextOperator:=xlContains
Col.FormatConditions.Add Type:=xlTextString, String:="N/A", _
TextOperator:=xlContains

With Col.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With

With Col.FormatConditions(2).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Col.FormatConditions(1).StopIfTrue = False




'Sort by conditional formatting
Set lrow = None_check_tab.Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
Set fmrange = Range(Cells(4, 1), Cells(lrow.Row, 5))

None_check_tab.Sort.SortFields.Clear
None_check_tab.Sort.SortFields.Add Key:=Range( _
Cells(4, 2), Cells(lrow.Row, 2)), SortOn:=xlSortOnCellColor, order:=xlDescending, DataOption:= _
xlSortNormal
With None_check_tab.Sort
.SetRange Range(Cells(4, 1), Cells(lrow.Row, 5))
.header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Set none_lastrow = None_check_tab.Cells.Find(What:="_NONE", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

Set na_lastrow = None_check_tab.Cells.Find(What:="N/A", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)


If none_lastrow.Row > na_lastrow.Row Then
LastRow = none_lastrow.Row
Else
LastRow = na_lastrow.Row
End If

Rows(LastRow + 1 & ":" & Rows.Count).Delete
Cells.FormatConditions.Delete




End Sub

===========================

 

[DanteAmor]

You are using .Find method to find a string, but the string does not exist then the objects are empty, you must first ask if they are not empty:

If not none_lastrow is nothing then
If not na_lastrow is nothing then
If none_lastrow.Row > na_lastrow.Row then
'Your code
End if
Else
Msgbox "no exists na"
End if
Else
Msgbox "not exists none"
End if