Find Next within For

[AlexCS]

Hi everyone,

I have a list of production lines in one worksheet, say
Production Line 1
Production Line 2

I go through the list of production lines with the help of For and a variable that takes the name of each line in turn then I go into another list and for each production line a variable retains the name of the component products and filters another worksheet by each product in turn

<TABLE style="WIDTH: 175pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=234><COLGROUP><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 122pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=163>Production Line 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=71>Product 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Production Line 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Product 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Production Line 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Product 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Production Line 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Product 5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">
</TD></TR></TBODY></TABLE>
However, my code will only identify the first Production Line 2 and I have no idea how to execute a Find Next within a For

Sheets("WS2").Select
ActiveSheet.Cells(1, 7).Select

var2=1

For a = 0 To 30
var1 = Sheets("WS1").Cells(9 + a, 12).Value
If var1 <> "Grand Total" And var1 <> "" Then
Worksheets("WS2").Columns("G").Find(What:=var1, After:=Cells(var2, 7), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
var2 = ActiveCell.Row
var3 = Sheets("WS2").Cells(var2,8).Value
Cells.FindNext(After:=ActiveCell).Activate
End If
Next a

Any ideas would be of great help!

Thanks a lot,

Alex

 

[CharlesChuckieCharles]

look at .Findnext

In the help