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

[kmht0307]

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:

[TABLE="width: 513"]
<tbody>[TR]
[TD="width: 513"]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'[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[ParamRay]

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:

= 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!

 

[kmht0307]

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)?

 

[MoshiM]

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.

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

 

[kmht0307]

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:

[TABLE="width: 513"]
<tbody>[TR]
[TD="width: 513"]MANITOU FINANCE LI X0008784 U1014777 DDR
[TABLE="width: 513"]
<tbody>[TR]
[TD="width: 513"]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.[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[MoshiM]

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.

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

 

[MoshiM]

[COLOR=#333333]'If instr(1,WS.RANGE("M" & X).value2,C_Ref)>0 then[/COLOR]

Should be

[COLOR=#333333]'If instr(1,WS.RANGE("M" & X).value2,Item)>0 then[/COLOR]