Can you use an Array within an IF statement, using AND as well?

[cbarryb]

Hi All, I have compiled this code, but I want to tidy this up and if possible, have arrays so that it would be easier to add more variables in the future.

In a nutshell I want the macro to look at Column AA for anything in the array AND if column AC has a specific text to then give me the result in Column AC, which my code does, but everytime I have a new varient for the array, the code looks more and more untidy. I feel if I have an array that would look for X, Y, Z and another that looks for A,B,C,D and another array for F,G,H,I,J - AND the merchant name, to then change the merchant name to type as well (thats just a simple rename) I'm just getting stuck on the Array element, my code is:

iCell = 1


Do Until Range("AC" & iCell) = ""
    If Range("AC" & iCell).Offset(0, -24) = "AmEx" Then
    
        Range("AC" & iCell).Value = "Books03 AmEx"

        GoTo NxtLine
    
    ElseIf Range("AC" & iCell).Offset(0, -2) Like "*?????PW*" Or Range("AC" & iCell).Offset(0, -2) Like "*200#####*" Or Range("AC" & iCell).Offset(0, -2) Like "*????pw*" And Range("AC" & iCell) = "Books03" Then

        Range("AC" & iCell).Value = "Books03 Upload"
        
    ElseIf Range("AC" & iCell).Offset(0, -2) Like "*.*.*.*" Or Range("AC" & iCell).Offset(0, -2) Like "* *.*.*" And Range("AC" & iCell) = "Books03" Then
    
        Range("AC" & iCell).Value = "Books03 F28"
    
    End If

NxtLine:

iCell = iCell + 1
Loop

Any help would be much appreciated, I feel like I am going around in circles at the moment.

 

[RobP]

Hi,

yes, it should be okay, does this array code get you where you need to be ?

Sub array_code()

Dim my_arr As Variant

last_row = ActiveSheet.Cells(Rows.Count, 29).End(xlUp).Row  'check for last row used in Col 29 ("AC")
my_arr = ActiveSheet.Range(Cells(1, 1), Cells(last_row, 29)).Value 'assumes data starts in cell A1, ends in AC(last_row), on "Sheet1"

For row_num = 1 To last_row
    
    If my_arr(row_num, 5) = "AmEx" Then 'check col 5 ("E") for "Amex"
        my_arr(row_num, 29) = "Books03 AmEx"
    
    ElseIf my_arr(row_num, 27) Like "*?????PW*" Or my_arr(row_num, 27) Like "*200#####*" Or my_arr(row_num, 27) Like "*????pw*" And my_arr(row_num, 29) = "Books03" Then
        my_arr(row_num, 29) = "Books03 Upload"
        
     ElseIf my_arr(row_num, 27) Like "*.*.*.*" Or my_arr(row_num, 27) Like "* *.*.*" And my_arr(row_num, 29) = "Books03" Then
    
        my_arr(row_num, 29) = "Books03 F28"
    
    End If

Next row_num

ActiveSheet.Range("A1:AC" & last_row).Value = my_arr 'dump my_arr back onto the workbook in its entirety

End Sub

 

[RobP]

Hi Barry, sorry, I re-read your question, and realised in my haste I'd not hit the mark for you.

So I created some code below, which basically puts your "groups" of strings into an array, then calls a separate function process to work on each array of strings (I created 2, each with its own function).
Hopefully you can follow and understand it. I left your initial "AmEx" check as a one off check, but if you plan to have "Visa" or "Mastercard" also in this column, then you could also create another array and function like the ones I did below.

The idea of the "process" functions is that they go and check each item in your arrays of "strings" and return a TRUE/FALSE if a match is found. That then gets "AND" with the check for column29 back in the main program.

Let me know how you get on.

Sub array_code()

Dim main_arr(), my_arr1(), my_arr2() As Variant
Dim row_num As Double

my_arr1 = Array("*?????PW*", "*200#####*", "*????pw*") 'define 1st array of strings here - you can add more as necessary
my_arr2 = Array("*.*.*.*", "* *.*.*")                   'define 2nd array of strings here


last_row = ActiveSheet.Cells(Rows.Count, 27).End(xlUp).Row  'check for last row used in Col 27 ("AA")
main_arr = ActiveSheet.Range(Cells(1, 1), Cells(last_row, 29)).Value 'assumes data starts in cell A1, ends in AC(last_row), on "Sheet1"


For row_num = 1 To last_row
    
    If main_arr(row_num, 5) = "AmEx" Then 'check col 5 ("E") for "AmEx" - you could also set this up as "Process3" with an array like process1,2
        main_arr(row_num, 29) = "Books03 AmEx"
    
    ' check for maches in the first array (my_arr1)
    ElseIf process1(main_arr, my_arr1, row_num) And main_arr(row_num, 29) = "Books03" Then
        main_arr(row_num, 29) = "Books03 Upload"
        
     'check for matches in the second array (my_arr2)
    ElseIf process2(main_arr, my_arr2, row_num) And main_arr(row_num, 29) = "Books03" Then
        main_arr(row_num, 29) = "Books03 F28"
    
    End If

Next row_num

ActiveSheet.Range("A1:AC" & last_row).Value = main_arr 'dump my_arr back onto the workbook in its entirety

End Sub

Function process1(main_arr() As Variant, my_arr1() As Variant, row_num) As Boolean

Dim a
process1 = False

For a = 0 To UBound(my_arr1(), 1) 'define count to cycle all my_arr1() items
       
    If main_arr(row_num, 27) = my_arr1(a) Then
        process1 = True
        Exit For
    End If
Next a

End Function

Function process2(main_arr() As Variant, my_arr2() As Variant, row_num) As Boolean

Dim a
process2 = False

For a = 0 To UBound(my_arr2(), 1) 'define count to cycle all my_arr2() items
       
    If main_arr(row_num, 27) = my_arr2(a) Then
        process2 = True
        Exit For
    End If
Next a

End Function

 

[cbarryb]

Hi Barry, sorry, I re-read your question, and realised in my haste I'd not hit the mark for you.

So I created some code below, which basically puts your "groups" of strings into an array, then calls a separate function process to work on each array of strings (I created 2, each with its own function).
Hopefully you can follow and understand it. I left your initial "AmEx" check as a one off check, but if you plan to have "Visa" or "Mastercard" also in this column, then you could also create another array and function like the ones I did below.

The idea of the "process" functions is that they go and check each item in your arrays of "strings" and return a TRUE/FALSE if a match is found. That then gets "AND" with the check for column29 back in the main program.

Let me know how you get on.

Sub array_code()

Dim main_arr(), my_arr1(), my_arr2() As Variant
Dim row_num As Double

my_arr1 = Array("*?????PW*", "*200#####*", "*????pw*") 'define 1st array of strings here - you can add more as necessary
my_arr2 = Array("*.*.*.*", "* *.*.*")                   'define 2nd array of strings here


last_row = ActiveSheet.Cells(Rows.Count, 27).End(xlUp).Row  'check for last row used in Col 27 ("AA")
main_arr = ActiveSheet.Range(Cells(1, 1), Cells(last_row, 29)).Value 'assumes data starts in cell A1, ends in AC(last_row), on "Sheet1"


For row_num = 1 To last_row
   
    If main_arr(row_num, 5) = "AmEx" Then 'check col 5 ("E") for "AmEx" - you could also set this up as "Process3" with an array like process1,2
        main_arr(row_num, 29) = "Books03 AmEx"
   
    ' check for maches in the first array (my_arr1)
    ElseIf process1(main_arr, my_arr1, row_num) And main_arr(row_num, 29) = "Books03" Then
        main_arr(row_num, 29) = "Books03 Upload"
       
     'check for matches in the second array (my_arr2)
    ElseIf process2(main_arr, my_arr2, row_num) And main_arr(row_num, 29) = "Books03" Then
        main_arr(row_num, 29) = "Books03 F28"
   
    End If

Next row_num

ActiveSheet.Range("A1:AC" & last_row).Value = main_arr 'dump my_arr back onto the workbook in its entirety

End Sub

Function process1(main_arr() As Variant, my_arr1() As Variant, row_num) As Boolean

Dim a
process1 = False

For a = 0 To UBound(my_arr1(), 1) 'define count to cycle all my_arr1() items
      
    If main_arr(row_num, 27) = my_arr1(a) Then
        process1 = True
        Exit For
    End If
Next a

End Function

Function process2(main_arr() As Variant, my_arr2() As Variant, row_num) As Boolean

Dim a
process2 = False

For a = 0 To UBound(my_arr2(), 1) 'define count to cycle all my_arr2() items
      
    If main_arr(row_num, 27) = my_arr2(a) Then
        process2 = True
        Exit For
    End If
Next a

End Function

Thank you and this is exactly what I'm looking for to easily add to the array when a new varient comes along, but when processing the Elseif's they dont seem to find a match in Col 27 (AA) from my_arr1. I have attached an image of the sheet, Col E looks for the amext, which is working, but the others need checking. I was trying to decipher the code, but I get a little lost when its areas I'm not familiar with, sorry.

 

[RobP]

Hi Barry,

I cant see any rows in your data that actually have the series of characters (for example "*200#####*") that you are searching for in your array. So ordinarily, from what I can see, it's doing what you wanted - and I'd expect to see nothing else.

Now, what I think you are alluding to, is that the string of characters (*200#####*) in the array, you are hoping will be wildcards and pick up patterns - but sadly thats not how it works.

Please let me have some more details on what you're expecting to see if we can help more. The sample data is very useful in that respect. For example, Row3, what would you expect to see, and likewise, Row6 ?

cheers
Rob

 

[RobP]

ah, sorry, in fact, what puzzled me was that you said it was working before .... (with your own code..) ..

I realised I' m looking for an exact match with an "=".

Try changing both process functions like this: (replace my = sign with the word Like in both process1 and process2 functions

 If main_arr(row_num, 27) Like my_arr1(a) Then

 

[cbarryb]

ah, sorry, in fact, what puzzled me was that you said it was working before .... (with your own code..) ..

I realised I' m looking for an exact match with an "=".

Try changing both process functions like this: (replace my = sign with the word Like in both process1 and process2 functions

 If main_arr(row_num, 27) Like my_arr1(a) Then

Thank you for this, its working a treat. I knew someone would have the know-how, and you did

 

[RobP]

Great, thanks Barry - appreciate the feedback. Glad to have been able to help.

cheers
Rob