ForEach loop object required error

By | February 27, 2018
Questions:

Summary: I have a list of policy numbers I’m looping through in Column A using the for each loop

Problem: Everything works except if there is an empty cell in Column A, my code deletes the entire row (as it should), but then when I try to set the policy variable I get a object required error. I’ve marked in my code where the error occurs.

Question: How can I delete empty rows without theCell losing its object?

The Code:

Dim theRange As Range
    Dim theSheet As Worksheet
    Dim theDate As String, policy As String, amount As String, details As String, entryDate As String

    Set theSheet = Sheets("OneDate")
    Set theRange = Range("A2:A" & theSheet.UsedRange.Rows.Count)

    For Each theCell In theRange                'FOR EACH POLICY IN COLUMN "A"

        If theCell.Value = "" Then

            theCell.EntireRow.Delete      '<-- Row deleted here
            MsgBox (theCell.Value)

        End If

        policy = theCell.Value            '<-- Error occurs here
        theDate = theCell.Offset(0, 1).Value
        theDate = UCase(Format(theDate, "ddMMMyy"))

Thanks in Advance for any help! 🙂

Answers:

Here’s a different way to do what you want.

Leave out the loop. From previous experimentation if you are looping through rows using for, each time you delete a row you end up skipping the row after the one you deleted. Also, as you noted the range you delete can no longer be referenced because you deleted it.

To delete all of the blank rows based upon the first column update your code to this:

Dim theRange As Range
        Dim theSheet As Worksheet
        Dim theDate As String, policy As String, amount As String, details As String, entryDate As String

        Set theSheet = Sheets("OneDate")
        Set theRange = Range("A2:A" & theSheet.UsedRange.Rows.Count)
'Editted in some perfunctory error trapping incase of no blank rows.
on error resume next
debug.print theRange.SpecialCells(xlCellTypeBlanks).count
on error goto 0
if err.number = 0 then
    theRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
end if

Once you’ve removed the blanks, THEN do your loop for the other checks.

Questions:
Answers:

I assume you only care about theCell if it is not empty. Here’s an update to your code, adding an Else to your If structure

Dim theRange As Range, lLastRow as long, lRowLoop as long
    Dim theSheet As Worksheet
    Dim theDate As String, policy As String, amount As String, details As String, entryDate As String

Set theSheet = Sheets("OneDate")
Set theRange = Range("A2:A" & theSheet.UsedRange.Rows.Count)
lLastRow =cells(rows.count,1).end(xlup).row

For lRowLoop=lLastRow to 2 step-1 'from last row to first row

    set theCell=cells(lRowLoop,1)

    If theCell.Value = "" Then

        theCell.EntireRow.Delete      '<-- Row deleted here
        MsgBox (theCell.Value)

    Else 

    policy = theCell.Value            '<-- Error occurs here
    theDate = theCell.Offset(0, 1).Value
    theDate = UCase(Format(theDate, "ddMMMyy"))

    End If

Leave a Reply

Your email address will not be published. Required fields are marked *