Run Time Error 91 : Object Variable or with Block Variable not Set

[haribole]

Option Explicit
Sub Button1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim FarmerName() As String
Dim PurchasedAmount() As Long
Dim LastRow, NameRow, AmountRow As Long
Dim i, j, k As Integer
i = 1
j = 1
k = 1
Worksheets(1).Activate
Range("F:F").NumberFormat = "General"
LastRow = ActiveSheet.UsedRange.Rows.Count
ReDim FarmerName(1 To LastRow) As String
ReDim PurchasedAmount(1 To LastRow) As Long
For i = 1 To UBound(FarmerName)
FarmerName(i) = Worksheets(1).Range("I" & i).Value
Next i
For j = 1 To UBound(PurchasedAmount)
PurchasedAmount(j) = Worksheets(1).Range("F" & j).Value
Next j
For k = 1 To LastRow Step 1
NameRow = Worksheets(2).Range("I:I").Find(What:=FarmerName(1), SearchDirection:=xlNext, MatchCase:=True).Row
' here the problem arising
AmountRow = Worksheets(2).Range("F:F").Find(What:=PurchasedAmount(1), SearchDirection:=xlNext, MatchCase:=True).Row
If NameRow = AmountRow Then
Worksheets(2).Rows(NameRow).Delete
End If
Next k
'Worksheets(2).Activate
'ActiveSheet.SaveAs Filename:="_revised_" + Replace(Date, "-", ""), FileFormat:=xlCSV, CreateBackup:=True
End Sub

Can anybody help me ? Please

 

[RoryA]

Welcome to the forum.

The most likely cause of that error, since you didn’t say where it happens, is that one of the Find operations is failing. You should always test the result before trying to refer to the found cell. What do want to do if one, or both, Find operations fails?

 

[haribole]

Thanks you very much for your valuable comments !!!

I have 2 sheets, Sheet1 containing some farmer name in I column and related purchased amount in F column and same exist in Sheet2.
I want to find those and delete the row.
For you information that both data must be in Sheet2 and if both match the row should be deleted.

Actually the code were:

For k = 1 To LastRow Step 1
NameRow = Worksheets(2).Range("I:I").Find(What:=FarmerName(k), SearchDirection:=xlNext, MatchCase:=True).Row
AmountRow = Worksheets(2).Range("F:F").Find(What:=PurchasedAmount(k), SearchDirection:=xlNext, MatchCase:=True).Row
If NameRow = AmountRow Then
Worksheets(2).Rows(NameRow).Delete
End If
Next k

 

[RoryA]

Try this:

Sub Button1_Click()
    Dim ws As Worksheet
    Dim FarmerName() As String
    Dim PurchasedAmount() As Long
    Dim LastRow As Long, NameRow As Long, AmountRow As Long
    Dim i As Long, j As Long, k As Long
    Dim foundRange As Range
    i = 1
    j = 1
    k = 1
    
    Application.ScreenUpdating = False
    
    With Worksheets(1)
        .Range("F:F").NumberFormat = "General"
        LastRow = .UsedRange.Rows.Count
        ReDim FarmerName(1 To LastRow) As String
        ReDim PurchasedAmount(1 To LastRow) As Long
        For i = 1 To UBound(FarmerName)
            FarmerName(i) = .Range("I" & i).Value
        Next i
        For j = 1 To UBound(PurchasedAmount)
            PurchasedAmount(j) = .Range("F" & j).Value
        Next j
    End With
    For k = 1 To LastRow Step 1
        Set foundRange = Worksheets(2).Range("I:I").Find(What:=FarmerName(k), SearchDirection:=xlNext, MatchCase:=True)
        If Not foundRange Is Nothing Then ' check that the farmer name was found
            If Worksheets(2).Cells(foundRange.Row, "F").Value = PurchasedAmount(k) Then
                foundRange.EntireRow.Delete ' delete row if amount matches as well
            End If
        Else
            MsgBox "Farmer: " & FarmerName(k) & " not found"
        End If
    Next k
End Sub

As a side note, when you write:

Dim LastRow , NameRow, AmountRow As Long

only the last variable is actually a Long, the others are Variants. You have to specify the type for each one:

Dim LastRow As Long, NameRow As Long, AmountRow As Long

 

[haribole]

Thank you very much it is working !!!

 

[RoryA]

Glad to help.

 

[haribole]

First of all Thank you very much !!!

I have change little in the said code:

Dim MyFolder As String, MyFile As String, MySheetName As String, ShellComand As String
ShellComand = "cmd.exe /k ""cd " & """ & ThisWorkbook.path & """ & " ren *.csv *.txt"""
MyFolder = "D:\Revise"
MyFile = Dir(MyFolder & "\*.txt")
Workbooks.OpenText Filename:=MyFolder & "" & MyFile, StartRow:=1, DataType:=xlDelimited, Comma:=True, Tab:=False, Semicolon:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), Array(4, xlTextFormat))
MySheetName = Replace(ActiveWorkbook.Name, ".txt", "")
ActiveWorkbook.Sheets(1).Copy After:=Workbooks("Revise.xlsm").Worksheets(Sheets.Count)
ActiveSheet.Name = MySheetName
For Each wb In Workbooks
If Not wb.Name = ThisWorkbook.Name Then
wb.Close SaveChanges:=False
End If
Next wb
Worksheets(2).Activate
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=MyFolder & "" & MySheetName + "_Rev_" + Replace(Date, "-", ""), FileFormat:=xlCSV, CreateBackup:=True
'problem here: not saving cells as text
Application.DisplayAlerts = True
Shell ShellComand
'Problem here: can't rename into .txt


Please help me !!!