Hi Everyone

I have the following problem and will appreciate your help:

I have the following code 
Private Sub mens()
Dim Division, Division1, Division2 As String
Dim No As String
Dim Divs As Range
Dim r As Range
Dim r1 As Range
Dim r2 As Range
Dim DataRange As Range
Dim i As Integer
Dim Rows As Long

Division = "Mens"

Set r = Range("C2:C" & Range("C2").End(xlDown).Row)
Set r1 = Range("A2:A" & Range("A2").End(xlDown).Row)

  
   
     For Each Divs In r
      If Divs.Value = Division Then
        Division2 = Divs.Value
        MsgBox "there is a Match" & Division2
        No = Cells(r1.Row, "A").Value
        MsgBox "Loyalty No is " & No
       
      End If
     Next
The code looks into Column C and if the division = Mens then it will return the value of the cell in Col A for that row. When I run this the No value aleays stays the same , i.e. in C1 and C5 are equal to Mens but I always get the value of the cell in A2 for NO.

Is the a way I can update the rows so that it returns the NO for the corresponding value when Col C contains Mens.

My second Query is if possible I need do the following:
if value in C Column is Mens then then copy every Customer no from Co A which are duplicated to another sheet (sheet2). In example below Customer 7322 is duplicate and one of the dept is Mens so copy all the rows where No is 7322 to sheet 2. This will not be the case for No 7439 as the departmets does not include Men.

7322 150 Mens Duplicate
7322 1.76 Food and Beverages Duplicate
7439 104.74 Restaurants Duplicate
7439 60 Shoe Heaven Duplicate
8288 60.98 Childrenswear Duplicate