Change this Macro so it finds all occurrences not just one

[tonywatsonhelp]

Hi everyone,

I have this code below that when i click on a cell it will look down a list of data and find when the sale happened,
however we offten get mutiple occurances of the same thing so I was woundering if it was possible to edit it so the message box list all the times that value happened not just the first time.

Here my code and below i'll explain in more detail:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge = 1 Then
    If Target.Value <> "" Then
    If Not Intersect(Range("D14:HR29"), Target) Is Nothing Then
    C1yy = Split(Target.Address, "$")(1) 'this just gives me the column letter of the cell I clicked so i can look down the correct raw data
    
   If ActiveSheet.Range(C1yy & 13).Value = "Biggest Sale" Then 'this is the header that I do this on so if its a different row it does not run
    cat1 = Target.Value 'i.e. "20.95"

Set rgFound = ActiveSheet.Range(C1yy & "30:" & C1yy & "5000").Find(cat1, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False) ' so this is the bit where it finds when "20.95" happened, but if I had two or more sales at the same value it only shows me the first, can this be edited to show me all of them?

    MsgBox "Sale Happened at:- " & Format(rgFound.Offset(0, -2).Value, "hh:mm"), , "" 'I would like this to show all times it happend, idealy with a new line for each time.
    End If
    End If
    End If
    End If
End Sub

so that's what I'm after, please help if you can as I'm really stuck on this one and I'm working on a Sunday so would love to get this fixed.

Thanks

Tony

 

[Skyybot]

I can't test without your Workbook but give this a shot. Please test on a copy of your Workbook in case code gives unexpected results.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge = 1 Then
    If Target.Value <> "" Then
    If Not Intersect(Range("D14:HR29"), Target) Is Nothing Then
    C1yy = Split(Target.Address, "$")(1) 'this just gives me the column letter of the cell I clicked so i can look down the correct raw data
    
   If ActiveSheet.Range(C1yy & 13).Value = "Biggest Sale" Then 'this is the header that I do this on so if its a different row it does not run
    cat1 = Target.Value 'i.e. "20.95"

Set rgFound = ActiveSheet.Range(C1yy & "30:" & C1yy & "5000").Find(cat1, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False) ' so this is the bit where it finds when "20.95" happened, but if I had two or more sales at the same value it only shows me the first, can this be edited to show me all of them?

    msg = "Sale Happened at:- " & Format(rgFound.Offset(0, -2).Value, "hh:mm") & vbNewLine  'I would like this to show all times it happend, idealy with a new line for each time.
    
    Set nxtCell = rgFound
    Do
        Set rgFound = ActiveSheet.Range(C1yy & "30:" & C1yy & "5000").FindNext(After:=rgFound)
        If rgFound.Address = nxtCell.Address Then Exit Do
            msg = msg & "Sale Happened at:- " & Format(rgFound.Offset(0, -2).Value, "hh:mm") & vbNewLine
    Loop
    MsgBox msg
    End If
    End If
    End If
    End If
End Sub

 

[smozgur]

I can't test it (and I am really not happy using variables without declaration) but it should help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        If Target.Value <> "" Then
            If Not Intersect(Range("D14:HR29"), Target) Is Nothing Then
            C1yy = Split(Target.Address, "$")(1)
            
               If ActiveSheet.Range(C1yy & 13).Value = "Biggest Sale" Then
                cat1 = Target.Value
                    
                    Set rngdata = ActiveSheet.Range(C1yy & "30:" & C1yy & "5000")
                    
                    Set rgfound = rngdata.Find(cat1, LookIn:= _
                        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
                        , MatchCase:=False, SearchFormat:=False)
                        
                    Do While Not rgfound Is Nothing
                        DoEvents
                        msg = msg & "Sale Happened at:- " & Format(rgfound.Offset(0, -2).Value, "hh:mm") & vbrclf
                        Set rgnext = rngdata.FindNext(rgfound)
                        If rgnext.Row <= rgfound.Row Then Exit Do
                        Set rgfound = rgnext
                    Loop
                    If msg <> "" Then
                        MsgBox msg, , ""
                    End If
                        
                    MsgBox "Sale Happened at:- " & Format(rgfound.Offset(0, -2).Value, "hh:mm"), , ""
                End If
            End If
        End If
    End If
End Sub

 

[tonywatsonhelp]

Thank you Skyybot and smozgor, almoost identical solutions, so thank you for all your help,
thanks
Tony

 

[smozgur]

Thank you Skyybot and smozgor, almoost identical solutions, so thank you for all your help,
thanks
Tony

Glad to hear we were able to help.

Could you please mark @Skyybot's post (post #2) as the solution as marked solutions will help future readers? Thanks.

 

[smozgur]

Thank you Skyybot and smozgor, almoost identical solutions, so thank you for all your help,

Post #2 has been marked as the solution to help future readers.