CODEWITHSUNDEEP
vbaexcel
Jeff Manville
Jeff Manville

Reputation: 47

Strange Run-time error '424' when running a for loop

So I am getting a strange error when running this a part of my vba macro. The macro is supposed to go through a table of people and their information, and where the user specified in the GUI is, their row is set to "" afterwards the table is sorted.The data entered is validated, so the user can't enter a value that doesn't exist.

Here is the problematic code:

    Set destPpl = Worksheets("People").Range("A2")

    Do While (destPpl.value <> I1.value And destPpl.value <> "")
        Set destPpl = destPpl.Offset(1, 0)
    Loop

    For i = 1 To 5
        destPpl.value = "" 'debug error occurs here
        destPpl = destPpl.Offset(0, 1)
    Next i

It will delete the first column content, but then won't iterate through the rest of the columns. All the table references are correct. I have no idea what is wrong.

Thank you for any help

Upvotes: 2

Views: 505

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Change this: 

destPpl = destPpl.Offset(0, 1)

to this: 

Set destPpl = destPpl.Offset(0, 1)

My guess is that destPpl is not properly declared as a range, or you would have gotten the error on this line the first time through the loop.

What is happening is the first time through the loop destPpl is being change from a Range object to the value of destPpl.Offset(0, 1). So on the second loop destPpl does not have the .Value qualifier as it no longer is a range.

The proper declaration for destPpl is:

Dim destPpl as Range

Upvotes: 6

Related Questions