VBA 424 error is not logical

[akirson]

In the following code snippet, I am using the same two expressions in two places. In the first everything runs fine, but the second generates a run-time error '424' Object required. I have tried retyping the code in case there is a hidden character somewhere, I have rebooted, cleaned the registry, all to no avail. I would be grateful if someone could give me an idea of what's wrong...

I've highlighted the two lines: the one in Green gets by with no errors, but the line in Red generates the run-time error.

For i = lLastRow To 3 Step -1
        Set rgCountry = Cells(i, Range("Lab").Column)
        Set rgQty = Cells(i, Range("Qty").Column)
        Set rgNoMemoPC = Cells(i, Range("NoMemoPC").Column)
        Set rgMemoPC = Cells(i, Range("MemoPC").Column)
        Set rgSoldPC = Cells(i, Range("SoldPC").Column)
        Set rgSold = Cells(i, Range("Sold").Column)
        Set rgQtySold = Cells(i, Range("QtySold").Column)
        Set rgRemarks = Cells(i, Range("Remarks").Column)
        Set rgInvDate = Cells(i, Range("InvDate").Column)
        Set rgOrgSource = Cells(i, Range("OrgSource").Column)
        Set rgCustomer = Cells(i, Range("Customer").Column)
        Set rgLotZ = Cells(i, Range("Lot").Column)
        If rgCountry.Value = "EGL" Then
            If rgCountry.Value = "" Then rgCountry.Value = "USA"
            If rgCountry.Value = "US" Then rgCountry.Value = "USA"
            If rgCountry.Value = "BEL" Then rgCountry.Value = "BLG"
        End If
        'Combine Remarks and OrgSource (Office Comment)
        rgRemarks.Value = rgRemarks.Value & rgOrgSource.Value
        'Fix sell price to reflect actual, and mark as Sold
        [B]If rgQty.Value = 0 And rgInvDate.Value <> "" Then[/B]
            If Left(rgCustomer.Value, 6) = "ONLINE" Then
                rgSold.Value = "Y"
            Else
                rgSold.Value = "X"
            End If
            rgMemoPC.Value = rgSoldPC.Value
            rgNoMemoPC.Value = rgSoldPC.Value
                        
        End If
        'Flag invoices over 90 days
        If myDate - rgInvDate.Value < collDays Then
            rgInvDate.Value = ""
        Else
            If rgSold.Value <> "" Then rgInvDate.Value = "P"
        End If
        'Delete all rows with Lot starting with Z/
        If Left(rgLotZ.Value, 2) = "Z/" Then Rows(i).Delete
        'delete rows with zero Qty and no invoice
        [B]If rgQty.Value = 0 And rgInvDate.Value = "" Then Rows(i).Delete[/B]
    Next i

[AndreRet]

Try the following -

If rgQty.Value = 0 And rgInvDate.Value = 0 Then Rows(i).Delete

'OR

If rgQty.Value = 0 And rgInvDate.Value = vbNullString Then Rows(i).Delete
'I do however think that the last is incorrect because it seems that you are using a grid, which can't have an empty string. It can however have a zero value

[akirson]

I have solved the riddle. The problem happens when this line is executed:

If Left(rgLotZ.Value, 2) = "Z/" Then Rows(i).Delete

If this row is indeed deleted, then the next line:

If rgQty.Value = 0 And rgInvDate.Value = "" Then Rows(i).Delete

throws up the error. To resolve it I combined both lines into one If statement:

If Left(rgLotZ.Value, 2) = "Z/" Or (rgQty.Value = 0 And rgInvDate.Value = "") Then Rows(i).Delete

Works!

[AndreRet]

It all makes sense. Glad you could solve this. Happy coding.:)