CODEWITHSUNDEEP
vbaexcel
NoobMaster101
NoobMaster101

Reputation: 17

VBA: An object in a loop is going to "Nothing" and is giving me an "Object variable or With block variable not set" Error

I have a column with strings and I am going through it to find and correct errors. The following code goes through the column finds the error and replaces it with the correct string.

This is correcting PENNSILVANIA to PENNSYLVANIA.

Sub CleanMFG()
    Set Penn = Columns("R").Find(What:="PENNSILVANIA", LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Not Penn Is Nothing Then
        Address = Penn.Address
        Do
            Penn.Value = "PENNSYLVANIA"
            Set Penn = Columns("R").FindNext(Penn)
        Loop While Not Penn Is Nothing And Penn.Address <> Address
    End If

My problem is that after correcting all the instances of this error, the "Penn" object become Nothing and thus I get an error.

Any ideas on how I can deal with this are appreciated.

Upvotes: 1

Views: 183

Answers (2)

DisplayName
DisplayName

Reputation: 13386

Since Find() wraps back to the first found range you could skip the first substitution, loop through all remaining values, exit loop at wrapping back to the first skipped value and finally substitute it

Sub CleanMFG()
    Set Penn = Columns("R").Find(What:="PENNSILVANIA", LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Not Penn Is Nothing Then
        Address = Penn.Address
        Do
            Set Penn = Columns("R").FindNext(Penn)
            Penn.Value = "PENNSYLVANIA"
        Loop While Not Penn.Address <> Address
        Penn.Value = "PENNSYLVANIA"
    End If
End Sub

Upvotes: 0

PeterT
PeterT

Reputation: 8557

The problem is with how Excel evaluates a compound logical statement. Many languages would encounter Loop While Not Penn Is Nothing and detect that Penn Is Nothing and terminate the loop immediately. Excel VBA evaluates ALL parts of the logical check before deciding the logical result. Therefore your error is tripping because Penn.Address is actually nothing but is attempting to compare it to Address. That's what's throwing the error.

Rewrite your If and Loop to something like this

If Not Penn Is Nothing Then
    Address = Penn.Address
    Do
        Penn.Value = "PENNSYLVANIA"
        Set Penn = Columns("R").FindNext(Penn)
        If Penn Is Nothing Then
            Exit Do
        End If
    Loop While Penn.Address <> Address
End If

Upvotes: 1

Related Questions