Find all occurences of a value 1

[indupriya9]

I have the following code which finds one occurence of a value. But what I need to be able to do is to find all occurences of a particular value.

Do While Len(Trim(activecell.value)) <> 0
                
      oElective = activecell.value
      Worksheets("Trainee Summary").Select
      lrow = activecell.SpecialCells(xlLastCell).Row
      
Set fc = Worksheets("Trainee Summary").Columns("D").Find(oElective)
fc.Select
                   
                   
          Do While activecell.value <> oElective And activecell.Row <= lrow
          Set fc = Worksheets("Trainee Summary").Columns("D").FindNext(fc)
          fc.Select
                      
          Loop
                     
                   activecell.Offset(0, -1).value = "X"
                   activecell.Offset(0, -1).Font.FontStyle = "Bold"
                                    
                   activecell.Offset(0, 4).value = activecell.Offset(0, 3)
                   activecell.Offset(0, 4).Font.ColorIndex = 3
                   ActiveSheet.Calculate
                   Worksheets("TraineeUnits").Select
                    activecell.Offset(2, 0).Select
                   
                   
Loop

Can anyone help me to modify the above code to find all instances of the value?

Thanks is advance
ip

 

[AnotherHiggins]

Would the built-in functionality of Format > Conditional Formatting work for you?


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Have you had a look at the FINDNEXT example in help - ity does pretty much exactly what you want....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MikeGeitner]

Here's something I used to find the occurances of yellow highlighted cells. I've changed it to count cells that have a value of 1, which might work for you with some changes. It goes through all the sheets in the workbook and counts within the range of cells.

Public Sub CountYellow3()

Dim WS As Worksheet
Dim Totalsheets As Integer
Dim ThisRow As Integer
Dim ThisColumn As Integer
Dim YellowCount As Integer


For Each WS In ActiveWorkbook.Sheets
    
        Totalsheets = Totalsheets + 1
        
        'Cell range
        For ThisRow = 1 To 1000
        For ThisColumn = 1 To 50
            
            If WS.Cells(ThisRow, ThisColumn).Value = 1 Then
            YellowCount = YellowCount + 1
            End If
            
            
' this is the yellow highlighted cell counter
'
'                If WS.Cells(ThisRow, ThisColumn).Interior.ColorIndex = 6 Then
'                   YellowCount = YellowCount + 1
'                End If
  
  
            Next ThisColumn
        Next ThisRow
    Next WS
        
    MsgBox "Sheets: " & Totalsheets & "   Yellows: " & YellowCount
    
    
End Sub

 

[indupriya9]

Hi there

Thanks very much for all your replies. I think I have found out why my code is not working. It is because it is not going into the second Do Loop. i.e.,

Do While ActiveCell.value <> oElective And ActiveCell.Row <= lrow

Does anyone know why? or is there any other condition that I missed in the Do Loop?

John

I am new to VBA in excel, so I donot understand what you mean by format -- conditional formatting?

Thanks in advance
ip

 

[indupriya9]

Hi guys

I have solved my problem by changing the code in this way.

  Do While Len(Trim(ActiveCell.value)) <> 0
             oElective = ActiveCell.value
             Worksheets("Summary").Select
                   
             lrow = ActiveCell.SpecialCells(xlLastCell).Row
    With Worksheets("Summary").Columns("D")
    Set fc = .Find(oElective)
         'Search for the first occurrence of the item
          'If a match is found then
        If Not fc Is Nothing Then
             'Store the address of the cell where the first match is found in a variable
            FindAddress = fc.Address
            Do
                   fc.Offset(0, -1).value = "X"
                   fc.Offset(0, -1).Font.FontStyle = "Bold"
                                    
                   fc.Offset(0, 4).value = fc.Offset(0, 3)
                   fc.Offset(0, 4).Font.ColorIndex = 3
                   
                   'ActiveSheet.Calculate
                 'Search for the next cell with a matching value
                Set fc = .FindNext(fc)
                 'Search for all the other occurrences of the item 
          Loop While Not fc Is Nothing And fc.Address <> FindAddress
                    
        End If
    End With
    ActiveSheet.Calculate
    Worksheets("TraineeUnits").Select
                   
     ActiveCell.Offset(2, 0).Select
     Loop

This code works perfectly. But I have a small problem. If I am searching for a value for example 43, the above code finds all the occureneces of 43. i.e., it finds the cells with values 6743, 1243 etc where the actual cell value is not just 43 but 6743, 1243 etc.

I would like to find the exact value of 43. Is there a parameter I can use with the find function?

Thanks in advance
ip

 

[PHV]

You may try this:
Set fc = .Find(oElective, LookAt:=xlWhole)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[indupriya9]

Hi PH

That was the exact solution I was looking for.

Thanks again. Here goes another star for you.

Regards
ip