FindNext Problem

[Della]

Hi guys,

I've been trying to write a code that looks for all references of a given number (Plan6.Cells(conter2, 2)) in a sheet (Plan8), and then pastes the information of other cells in the same row at a different sheet ( the ActiveSheet, Plan7 in this case). The problem is that my FindNext function doesn't work, just the first Find. Because of that my loop runs only one time, since the adresses don't change. Here's the part of the code that's not working

With Plan8.Columns("C")
Set spo = .Find(Plan6.Cells(conter2, 2), LookIn:=xlFormulas, _
lookat:=xlWhole, searchdirection:=xlNext)
firstAddress = spo.Address
Do
ActiveCell.Offset(1, 0).Select
ActiveCell = spffset(0, 1)
Set spo = .FindNext(after:=spo)
Loop While Not spo Is Nothing And spo.Address <> firstAddress
End With


I'm trying to learn vba by myself and by using this forums, so any help would be great.

Best regards from Brazil!

 

[MrKowz]

Welcome to the forums!

Try omitting the After:= part:

With Plan8.Columns("C")
    Set spo = .Find(Plan6.Cells(conter2, 2), LookIn:=xlFormulas, _
                  lookat:=xlWhole, searchdirection:=xlNext)
    firstAddress = spo.Address
    Do
        ActiveCell.Offset(1, 0).Value = spo.Offset(0, 1).Value
        Set spo = .FindNext([B][COLOR=red]spo[/COLOR][/B])
    Loop While Not spo Is Nothing And spo.Address <> firstAddress
End With

 

[Della]

Nope, still goes straight to the End With part.

I have no idea what's wrong, maybe something about the FindNext function is searching at a sheet that's not the active one, i don't know.

The value of Plan6.Cells(conter2, 2) that it looks for in Plan8 is an item, like 4.2.1.1 and i would like the program to get information in all rows that have that item. Then the conter2 variable changes, so that it now looks for the next one, like 4.2.1.2.

Should I post more of the code?

Thanks for the response MrKowz!

 

[GTO]

Try Plan8.Range("C:C")

 

[Della]

Just found out what was wrong! The guy that sends me the sheets with the itens had just typed the first 4.2.1.1 and equaled the other related cells to that one, that's why the FindNext couldn't find anything. Sure looks stupid now, lol

Thanks for the help anyway!