VBA to search a text string to check if a value from a list is part of the text string
Results 1 to 7 of 7

Thread: VBA to search a text string to check if a value from a list is part of the text string
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to search a text string to check if a value from a list is part of the text string

    Good morning,

    I have a downloaded file of bank transactions and in the description column, that contains a string of text and numbers, I want to be able to establish whether a customer reference, which could be numbers or text, is part of the description string.

    So for instance:

    The description on one line is:

    FRANCOTYP POSTALIA 503747 058027 DDR

    In my list of customers, the reference number for a customer is 058027 which matches the above string, so I want to return the value 'AR' in the cell to the right of the description column, which is column 'N'

    If it is then I want to be able to categorise that transaction as being 'AR'.

    I have tried to use INSTR but that only seems to work if you have a specific search criteria.

    Any help would be appreciated.

  2. #2
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,192
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to search a text string to check if a value from a list is part of the text string

    Hello. No need to use VBA here; you can do this with a normal worksheet function.

    Suppose your description is in cell N2, and your list of customer references are in A1:A20.

    Then you can enter this as an array formula (i.e. via Ctrl+Shift+Enter) into cell O2, and drag it down:

    Code:
    = IF( SUM( COUNTIF( N2, "*" & IF( $A$1:$A$20 = "", CHAR( 191 ), $A$1:$A$20 ) & "*" ) ) > 0, "AR", "" )
    IMPORTANT: You must enter this using Ctrl+Shift+Enter otherwise it won't work!
    Windows 10, Excel 365

  3. #3
    New Member
    Join Date
    Aug 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to search a text string to check if a value from a list is part of the text string

    Many thanks for this, the reason why I wanted to explore a VBA option is that the file is rather large and the user wanted to have a non formula solution to this. And also this step is part of a larger process and so it needs to be automated.

    Is there any way to use VBA to enter this array formula into each cell in the range N2 to say N100 (would be a growing or shrinking range)?

  4. #4
    Board Regular
    Join Date
    Jan 2018
    Posts
    185
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to search a text string to check if a value from a list is part of the text string

    I want to be able to establish whether a customer reference, which could be numbers or text, is part of the description string.
    Is there a predetermined number of substrings in the string if the customer reference is present? If absent?
    Does the placement of the customer reference change? If so, how do you differentiate it?

    The following code assumes the string is delimited by a space, string has at least 5 elements and if there are 5 or more then the customer reference is the 4th, and that input is in column M and output in Column N. If there are less than 5 elements then Nothing will happen.

    Set First_Row_WData= to the first row with data you want the code to run on.
    If you want the code to run till the bottom of the used range on the worksheet, then do nothing. Otherwise adjust Last_Row_WData.

    Code:
    Sub For_User()
    
    Dim STR() As String, L As Long, WS As Worksheet, First_Row_WData As Long, Last_Row_WData As Long
    
    With Application
                    .ScreenUpdating = False
                     .Calculation=xlCalculationManual
    End With
    
    
    Set WS = ThisWorkbook.Worksheets("Sheet Name goes here") 'You can replace this with a sheet codename
    
    
    ROW_Count = WS.UsedRange.Rows.Count
    
    
    First_Row_WData=2
    
    Last_Row_WData= ROW_Count
    
    For X = First_Row_WData To Last_Row_WData
    
    
        STR = Split(WS.Range("M" & X).Value2, " ") 'string is now delimited with a space
        
        If UBound(STR) >= 4 Then
        
            WS.Range("N" & X).Value2 = "AR"
            
        End If
        
    Next X
    
    
    With Application
                    .ScreenUpdating = True
                     .Calculation=xlCalculationAutomatic
    End With
    
    
    End Sub
    Last edited by MoshiM; Jun 23rd, 2019 at 08:29 PM.

  5. #5
    New Member
    Join Date
    Aug 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to search a text string to check if a value from a list is part of the text string

    Thanks MoshiM, usually the string would be as follows:

    Customer Name (could be spaces between), Customer Number, Another Reference(sometimes) and the text 'DDR'

    So for 2 examples of the actual data I am looking at:

    MANITOU FINANCE LI X0008784 U1014777 DDR
    DIRECT RESPONSE LT BS112030 DDR

    So I want to effectively look up the values 'X0008784' and 'BS112030' from a customer list and if they are present in the list return the text 'AR' and if not return the text 'DDR' in the column adjacent to the string.

    So the placement of the customer number could be different in each string.


  6. #6
    Board Regular
    Join Date
    Jan 2018
    Posts
    185
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to search a text string to check if a value from a list is part of the text string

    You will need to create a column that will hold Customer references that correlate to the string in the same row and column M.
    An alternative to this would be to place a unique character somewhere in the string if it holds a customer reference and make some minor adjustments to the instr function below. If the unique character is unwanted in the end, then you can use the replace function to remove it.

    You could set up an array to hold customer references as well and just search that.

    Code:
    Sub For_User()
    
    'Dim C_Ref() as string
    Dim STR As String,  WS As Worksheet, First_Row_WData As Long,Last_Row_WData As Long,Item as variant
    
    
    With Application
                    .ScreenUpdating = False
                    .Calculation=xlCalculationManual
    End With
    
    
    Set WS = ThisWorkbook.Worksheets("Sheet Name goes here") 'You can replace this with a sheet codename
    
    
    STR="Letter of column that will hold customer references in the same row that column M is searched"
    
    'C_Ref= WS.range("Range of customer references goes here").value2
    
    ROW_Count = WS.UsedRange.Rows.Count
    
    
    First_Row_WData=2
    
    
    Last_Row_WData= ROW_Count
    
    
    For X = First_Row_WData To Last_Row_WData
      
        If instr(1,WS.RANGE("M" & X).value2,WS.RANGE(STR & X).value2)>0 then
        
            WS.Range("N" & X).Value2 = "AR"
            
        End If
    'For each Item in C_Ref
            
    'If instr(1,WS.RANGE("M" & X).value2,C_Ref)>0 then
        
            'WS.Range("N" & X).Value2 = "AR"
            
        'End If
    'Next Item    
    'Next X
    
    
    With Application
                    .ScreenUpdating = True
                    .Calculation=xlCalculationAutomatic
    End With
    
    
    End Sub
    Last edited by MoshiM; Jun 24th, 2019 at 05:08 AM.

  7. #7
    Board Regular
    Join Date
    Jan 2018
    Posts
    185
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to search a text string to check if a value from a list is part of the text string

    Code:
    'If instr(1,WS.RANGE("M" & X).value2,C_Ref)>0 then
    Should be

    Code:
    'If instr(1,WS.RANGE("M" & X).value2,Item)>0 then

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •