I fixed the problem. the reason the code hung was if you were on row 10 and
the data was 8 in column C the macro would find 8 also in row 20 and 30 but
column L was empty in both cases. the code would get hung in a loop finding
8 in row 20, then 30, then 20, then 30 and never getting out of the code.
The problem with find and find next is it wraps and when it gets to the last
row goes back to the 1st row in the search field.


The code right now wraps that if it doesn't find the number at a row higher
than then current row it goes back to row 3 and searches until it gets to the
current row. So the result is it will put the data from column L on a lower
row instead of a higher row If you don't want it to wrap and put and leave a
blank if it doesn't find the number in a higher row then make this change

from:
Range("S" & RowCount) = Num
to:
if c.Row RowCount then
Range("S" & RowCount) = Num
end if

Sub GetNext()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 3 To (LastRow - 1)
Data = Range("C" & RowCount)
With Range("C3:C" & LastRow)
Set c = .Find(what:=Data, after:=Range("C" & RowCount), _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Num = Range("L" & c.Row)
Do While Num = "" And _
Not c Is Nothing And _
c.Row RowCount

Set c = .FindNext(after:=c)
Num = Range("L" & c.Row)
Loop

Range("S" & RowCount) = Num
End If
End With
Next RowCount

End Sub


"colwyn" wrote:


Joel Guest - thanks. Still not working. It works on the first range then
freezes. On 'debug' it highlights the line "Num = Range("L" & c.Row)"

???
Can it be fixed?
Big thanks.
Colwyn.


--
colwyn
------------------------------------------------------------------------
colwyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=34
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47019