Compare string within a string and list of cell values

Shanthan

New Member
Joined
Feb 24, 2013
Messages
47
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! :biggrin:
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
You're on the right track. Can you show your code even if it doesn't work properly?
 

Shanthan

New Member
Joined
Feb 24, 2013
Messages
47
Here is what I have:

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
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.

Code:
    [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

New Member
Joined
Feb 24, 2013
Messages
47

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
Code:
    [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

New Member
Joined
Feb 24, 2013
Messages
47
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top