Excel VBA - FINDNEXT Exit Loop

[ensorg]

Hi All,

Hope somebody can help me with the following. I have an excel workbook with a sheet or part numbers and a sheet of qty's. The code should look at the part number and get the qty from the next sheet, and if the is two qty entries for that part then sum them. It works fine if there is just one entry in the qty sheet, but when there is multiple entries it never seems to exit the "findnext" loop.
Anyone any ideas?

Private Sub comprtstk_Click()
Dim search, Qty, searchcounter, writecounter, mycell, mynextcell, address, address2, FirstAddress

searchcounter = 2
writecounter = 2
'do loop until no more parts
Do Until Sheets("PARTDETAIL&quot.Cells(searchcounter, 2) = ""
Qty = 0
'first part number
search = Worksheets("PARTDETAIL&quot.Cells(searchcounter, 2)
'search for part number
With Sheets("STKDETAIL&quot.Range("B1:B10000&quot
Set mycell = .Find(search)
If Not mycell Is Nothing Then
address = mycell.Row
FirstAddress = mycell.address
Qty = Worksheets("STKDETAIL&quot.Cells(address, 3)

Set mynextcell = .FindNext(mycell)
If Not mynextcell Is Nothing And mynextcell.address <> FirstAddress Then
Do
Set mynextcell = .FindNext(mycell)
address2 = mynextcell.Row
Qty = Qty + Worksheets(&quot;STKDETAIL&quot.Cells(address2, 3)
Loop While Not mynextcell Is Nothing Or mynextcell.address <> FirstAddress
End If
End If


Worksheets(&quot;partandstockdetail&quot.Cells(writecounter, 1) = Worksheets(&quot;partdetail&quot.Cells(searchcounter, 2)
Worksheets(&quot;partandstockdetail&quot.Cells(writecounter, 2) = Worksheets(&quot;partdetail&quot.Cells(searchcounter, 3) ' Des
Worksheets(&quot;partandstockdetail&quot.Cells(writecounter, 3) = Worksheets(&quot;partdetail&quot.Cells(searchcounter, 4) ' Supplier
Worksheets(&quot;partandstockdetail&quot.Cells(writecounter, 4) = Qty ' Quantity
Worksheets(&quot;partandstockdetail&quot.Cells(writecounter, 5) = Worksheets(&quot;partdetail&quot.Cells(searchcounter, 5) ' Min
Worksheets(&quot;partandstockdetail&quot.Cells(writecounter, 6) = Worksheets(&quot;partdetail&quot.Cells(searchcounter, 6) ' Lead
Worksheets(&quot;partandstockdetail&quot.Cells(writecounter, 7) = Worksheets(&quot;partdetail&quot.Cells(searchcounter, 7) ' Batch
writecounter = writecounter + 1
End With
searchcounter = searchcounter + 1

Loop



End Sub

 

[xlbo]

Just a guess really but:
Loop While Not mynextcell Is Nothing Or mynextcell.address <> FirstAddress

should it be:
Loop While Not mynextcell Is Nothing AND mynextcell.address <> FirstAddress

Using your code, only ONE of the statements has to be true to continue the loop
If it's found a cell then mynextcell is NOT nothing so is true and therefore doesn't matter if mynextcell=myfirstcell
HTH Rgds
~Geoff~

 

[ensorg]

I tried changing that but it still doesn't work. I'm not sure why it never exits the findnext loop.