Compare string within a string and list of cell values

[Shanthan]

Hi,

I have a string, which contains one or more names that I need to compare to a list of names in a worksheet called "User Names". If there is a match, I need to count how many of the names in "User Names" can be found in the string. One name can appear multiple times in the string or different names can appear in the string.

I tried to loop through the names in "User Names" worksheet and use InStr Function to find the position. If it's greater than 0, then there is a match. But, I can't seem to make it work properly. Is there any other efficient method that I can use. Can anyone suggest a VBA code to solve this?

Thank you!

 

[AlphaFrog]

You're on the right track. Can you show your code even if it doesn't work properly?

 

[Shanthan]

Here is what I have:

Dim BeginningOfOldComment As Integer
Dim UserNameToFindInOldComment As String
Dim UserNamePositionInOldComment As Integer
Dim LastRow As Integer
Dim CountOfUserNameInOldComment As Integer
        
CountOfUserNameInOldComment = 0
UserNamePositionInOldComment = 1
LastRow = Sheets("User Names").Range("A" & Rows.Count).End(xlUp).Row
        
For BeginningOfOldComment = 1 To Len(OldComment)
    For UserNameStart = 1 To LastRow
        UserNameToFindInOldComment = Sheets("User Names").Cells(UserNameStart, "A")
        If InStr(UserNamePositionInOldComment, OldComment, UserNameToFindInOldComment) > 0 Then
            UserNamePositionInOldComment = InStr(UserNamePositionInOldComment, OldComment, UserNameToFindInOldComment)
            CountOfUserNameInOldComment = CountOfUserNameInOldComment + 1
        End If
    Next
Next

I read that looping is slow. I read about .Find function in Excel. Is that something more efficient? Is comparing arrays faster?

 

[AlphaFrog]

This counts the user names within the string. A name is only counted once if it occurs within the string multiple times.

I'm not sure of your source for the string OldComment.

    [color=darkblue]Dim[/color] OldComment [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] UserNames [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], Counter [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    OldComment = ActiveSheet.Range("A1").Value  [color=green]'?Source?[/color]
    
    [color=darkblue]With[/color] Sheets("User Names")
        UserNames = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
    End [color=darkblue]With[/color]
            
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](UserNames, 1)
        [color=darkblue]If[/color] InStr(1, OldComment, UserNames(i, 1), 1) > 0 [color=darkblue]Then[/color] Counter = Counter + 1
    [color=darkblue]Next[/color] i
    
    MsgBox "Count of user names in old comment: " & Counter

 

[Shanthan]

Thanks so much for helping me. Yes, OldComment is the source. Is it possible to count the Name even if it's repeated? Let's say, the name "John" appears in OldComment 3 times, is it possible to get Count to equal 3?

 

[AlphaFrog]

    [color=darkblue]Dim[/color] OldComment [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] UserNames [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], Counter [color=darkblue]As[/color] [color=darkblue]Long[/color], j [color=darkblue]As[/color] Long
    
    OldComment = ActiveSheet.Range("A1").Value  [color=green]'?Source?[/color]
    j = Len(OldComment)
    
    [color=darkblue]With[/color] Sheets("User Names")
        UserNames = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
    End [color=darkblue]With[/color]
            
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](UserNames, 1)
        Counter = Counter + (j - Len(Replace(OldComment, UserNames(i, 1), "", Compare:=1))) / Len(UserNames(i, 1))
    [color=darkblue]Next[/color] i
    
    MsgBox "Count of user names in old comment: " & Counter

 

[Shanthan]

Thank you AlphaFrog. This is exactly what I wanted. I would not have been able to figure this out on my own. I have to figure out how your code works now though. It's amazing how much you know. Keep up the great work.

I was trying to re-size the height of the cell comment box while keeping the width of the box constant. I have a userform that generates cell comment with the username and time when a cell value is changed. I needed to be able to adjust the height as more comments are added to the same cell. I used the code in the link below count the number of lines in the string after text wrap occurs, and then I used your code to determine the number of times the username occurs, so that I can adjust the height accordingly. It all works perfectly.

Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line

 

[AlphaFrog]

You're welcome. I'm glad it works for you. For future reference, the description of why you are doing this would be more helpful at the start of the thread.