Extracting strings using arrays

[DavidSCowan]

I want to extract the brand names from a column of SKUs (stock keeping units). I know how to do it using standard worksheet functions (i.e. without using vba) and I know how to do it by simple looping. However I want to use arrays because there are several hundred thousand rows of SKUs and looping will take too long (I also want to improve my vba skills – the board has already helped me a lot with this) But I have got stuck in two places and it is driving me mad

Below are example SKUs.

Sure 24hr Intensive Unisex Aerosol Active 150ml Antipers
Addiction Female Aerosol Citrmusk 75ml Nonantpe
Sure For Men 24hr Intensive Male Aerosol Sensitiv 250ml Antipers
Arrid Extra Dry Unisex Aerosol Showfres 200ml Antipers
Soft & Gentle Unisex Aerosol Pureline 250ml Antipers
Adidas Female Aerosol Intensiv 250ml Antipers

The brand names are always followed by one of three gender tags – Unisex, Female or Male. So using this enables the brand names to be extracted. The brand names in the SKUs above are as follows:

Sure 24hr Intensive
Addiction
Sure For Men 24hr Intensive
Arrid Extra Dry
Soft & Gentle
Adidas

Here is the code with some annotations

Sub ExtractingBrandname()
[COLOR="SeaGreen"]'Starts By Finding The Position Of Gender Because The Brand Name Is The String 
'Up Until That Point
[/COLOR]
Dim SKU As Range
Dim Arr
Dim iArr
[COLOR="SeaGreen"]' Set to take all 7 elements of the SKU i.e. not just the brand name[/COLOR]
Set SKU = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp).Offset(, 7))
[COLOR="SeaGreen"]'Set cells in SKU to an Array[/COLOR][/COLOR]
Arr = SKU
'Loop through the array
       For n = 1 To UBound(Arr, 1)
    
[COLOR="SeaGreen"]'Create an array iArr out of the elements of Arr[/COLOR]
        iArr = Split(Arr(n, 1), " ")
[COLOR="SeaGreen"]'Find the array position of the words Unisex, Female or Male as these come 
'immediately after the brand name
'BUT I CAN'T GET THIS TO WORK - I GET "ELSE WITHOUT IF" ERROR WHAT AM I DOING 
'WRONG?[/COLOR]
            For i = 0 To UBound(iArr)                    
                If iArr(i) = "Male" Or iArr(i) = "Female" Or iArr(i) = "Unisex" Then
                            pos = i - 1
                            
                        End If
                        Exit For
                Else
                            
                Next i
[COLOR="SeaGreen"]'What I want to do now is to use pos to extract the words in the cell up 
'until the Unisex, Female or Male but I don't know how to do it!
Once I have got this I will write the array back onto the spreadsheet[/COLOR]

Can someone help please.

With kind regards

David

 

[Andrew Poulsom]

To solve your compile error just move the End If down below the Else.

 

[DavidSCowan]

Hi Andrew

Thank you very much for your speedy reply. Your suggestion removes the compile error okay but the macro simply jumps to the end bypassing everything!

David

 

[ScottR]

You don't need an else clause and need to take care in positioning your Exit For.

Try changing that section to

For i = 0 To UBound(iArr)                                     

If iArr(i) = "Male" Or iArr(i) = "Female" Or iArr(i) = "Unisex" Then                             
     pos = i - 1
     Exit For
End If

Next i

 

[Andrew Poulsom]

If you want to avoid looping why have you got two loops in your code?

 

[DavidSCowan]

YES! That works - thank you very much that is a great help.

Do you have any ideas how I now extract all the words up until and including the number given by pos? I have been trying to loop adding words up until the 'posth'' array position of iArr but I just can't get it. Another approach would be some kind of equivalent of LEFT if there is such a thing.

With kind regards

David