VBA: Splitting and Joining Arrays & Strings

Question

I'm dealing with a set of cells that looks something like this:

Cell setup

What I'm trying to do is filter through the Country column for each country, and then take each of the Mfr's and add them together so that I can filter for them all in another workbook.

So basically, I want to Join(m1, m2, m3) and this is how I'm trying to do it:

xRange is the column for "Mfr Abbreviation". I also have If xCell... <> "" Then because there are not "Previous Mfr Abbrev" for all entries in xRange

Dim tempMfr As String
Dim temp2Mfr As String
Dim stringArray() As String
Dim arraytostring As String

For Each xCell In xRange
    tempMfr = xCell

    If xCell.Offset(0, 2) <> "" Then
        temp2Mfr = xCell.Offset(0, 2)
        stringArray() = Split(temp2Mfr, ", ")
        arraytostring = Join(temp2Mfr & stringArray, ", ")
        stringArray() = Split(arraytostring, ", ")


        For x = LBound(stringArray) To UBound(stringArray)
            MsgBox (stringArray(x))
        Next
    End If

Next

My issue comes from the line Join(temp2Mfr & stringArray) due to the & creating a Type Mismatch Compile Error.

I've also tried:

ReDim Preserve stringArray(0 to (UBound(stringArray) + 1))
stringArray(UBound(stringArray)) = temp2Mfr

Once I get that sorted out, I plan on using a search in the other workbook that looks something like this:

Cells.AutoFilter Field:=4, Criteria1:= Array(MfrArray)

Thank you very much for your help.

Type Mismatch; edited original post. But even so, my method of going about this is what isn't working. — Tawm
– Tawm, Commented Jul 17, 2015 at 17:37

Excel Hero · Accepted Answer · 2015-07-17 18:13:46Z

2

Your are trying to concatenate a string to your array before Join even executes.

That's not permitted and is what is causing your error.

You can do this instead:

Redim Preserve stringArray(Ubound(stringArray) + 1)
stringArray(Ubound(stringArray)) = temp2Mfr

You do not need Join() at all.

edited Jul 17, 2015 at 18:13

answered Jul 17, 2015 at 17:52

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tawm Over a year ago

One error in both of our solutions. You need stringArray(Ubound(stringArray) + 1) and I needed to get rid of the 0 to. But that seems to have done it. Thank you very much. Change that and you've got an answer :)

Excel Hero Over a year ago

That's what I get for answering on my phone with no Excel... ;) Fixed the typo.

Tawm Over a year ago

As I go to the next entry in that xRange, how do I clear the Array and reset the dimensions? I'm trying to use Erase stringArray and ReDim stringArray but Erase leaves no dimensions to work with and ReDim gives a syntax error.

Excel Hero Over a year ago

Try changing your initial declaration of the array to Variant, like so: Dim stringArray As Variant. Notice no parentheses. Then you can create the array within the Variant on the fly by setting it to the results of the Split() function. That should work the first time... and every time subsequently.

Tawm Over a year ago

Hey again. I've been searching around but can't find anything useful. I need to filter a column for the array I've made with this code. I'm trying to use Cells.AutoFilter Field:=14, Criteria1:=stringArray, Operator:= but I don't know what the operator should be. An example of my issue is that something in the Array might be "Ta" when what's in the column is actually "Tawm". I'm thinking something like Operator:=xlContains but that's a no-go. Do you know how I could do this? I just want it to be like I'm typing in "Ta" and then selecting all the options which the autofilter finds.

|

Collectives™ on Stack Overflow

VBA: Splitting and Joining Arrays & Strings

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related