Excel VBA codes

Question

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

Darren Bartrup-Cook · Accepted Answer · 2016-07-26 10:07:13Z

2

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.

answered Jul 26, 2016 at 10:07

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

C.McK Over a year ago

Brilliant! Thanks for the speedy, helpful response :)

Collectives™ on Stack Overflow

Excel VBA codes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related