CODEWITHSUNDEEP
vbaexcel
C.McK
C.McK

Reputation: 3

Excel VBA codes

I am new to writing VBA code and have come across this code below online which is very similar to what I am trying to do. However when I try to run it, it highlights the line Loop While Not c Is Nothing And c.Address <> firstAddress and a message pops up saying "Object variable or With block variable not set".

Does anyone know how to fix this? Any help is much appreciated!

Sub Commodity()
'
' Commodity Macro
' Commodity
'
'
With Worksheets(1).Range("a1:a500")

   Set c = .Find(2, LookIn:=xlValues)
   If Not c Is Nothing Then
        firstAddress = c.Address
        Do
             c.Value = 5
             Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

End Sub

Upvotes: 0

Views: 141

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

The problem there is that you're looking for the number 2 and changing it to 5. Once it's changed all of them it will continue searching from the top again within the loop but c will equal nothing (as it couldn't find anything) on the second time around which causes the error when it tries to evaluate c.Address <> firstAddress.

As you're replacing the figures I'd remove that part and just leave Loop While Not c Is Nothing

On the other hand, if you're not changing values and just looking for them you'll need to include the c.Address <> firstAddress to stop it getting stuck in an endless loop.

Upvotes: 2

Related Questions