I have written a code, which includes the FindNext method. All the code works so far, only when it gets to the FindNext method it shows an error saying the FindNext Object cannot be assigned. However, I don't see where the Range Object (in this case "cell") is changed in any way for the FindNext method to not be able to assign it. Has anybody got an idea?
Please ignore any chunky written code, I'm very new with VBA
Please ignore any chunky written code, I'm very new with VBA
VBA Code:
Sub Search()
Dim wks As Worksheet
Set wks = ActiveSheet
Dim cell As Range, sngCell As Range
Dim Count As Long
Dim firstCell As String
Dim rg As Range, lastColumn As Long
Set cell = wks.Cells.Find(what:="Planned Supply at BP|SL (EA)", LookIn:=xlValues, lookat:=xlWhole)
firstCell = cell.Address
Do
With wks
lastColumn = .Cells(cell.Row, .Columns.Count).End(xlToLeft).Column
Set rg = Range(cell, .Cells(cell.Row, lastColumn))
End With
For Each sngCell In rg
If IsNumeric(sngCell.Value) = True Then
If sngCell.Value > 0 Then
If sngCell.Offset(-2, 0).Value > sngCell.Value Then
Count = Count + 1
End If
If Count = 0 Then
Rows(sngCell.Row - 2).Delete
Rows(sngCell.Row - 1).Delete
Rows(sngCell.Row).Delete
End If
End If
End If
Next
Set cell = wks.Cells.FindNext(cell)
Loop While cell.Address <> firstCell
End Sub