Excel VBA Join function

Question

I try to join the elements of a vector in vba.

First I search with a for loop specific strings.

CE_addr = FindAll(Workbooks("raw_data.xlsm").Worksheets("IQ_Key_Dev_Type").Cells(k, (p - 1)).Value, _
                Worksheets("Sheet1").Range("O:O"), xlValues, xlWhole).Address

Afterwards I use the address object to get the corresponding values one column to the right.

              rn = Split(CE_addr, ",")
              n = UBound(rn, 1) - LBound(rn, 1)

              For w = 0 To n
              ReDim CE_cod(n) As Variant
              CE_cod((w)) = Workbooks("raw_data.xlsm").Worksheets("CE_List").Range(rn(w))

              Next w

So in the CE_cod vector I save all the corresponding values (sometimes it's just one value, sometimes more than one)

And now I want to concatenate all the contained (text) values with ";" as delimiter:

Cells(j, 3) = Join(CE_cod, ";")

Let's assume CE_cod has two entries in the first loop: ["XXX" , "YYY"] and just one in the second loop: ["XXX"]

For some reason the result in Cell(j,3) in the first case (multiple values in CE_cod) is: ";YYY" (note that a delimiter is added, but not the expression itself). In the second case the result is: "XXX"

I checked if the vector CE_cod contains all values with:

Cells(j, (10 + w)) = CE_cod((w))

and indeed, when I do this "XXX" and "YYY" are contained.

So why does the join function skips the first entry of the vector when there are multiple entries?

PS: I found the "FindAll" function here: http://www.tushar-mehta.com/publish_train/xl_vba_cases/1001%20range.find%20and%20findall.shtml#_The_FindAll_function

score 1 · Accepted Answer · 2018-03-09 12:56:40Z

1

I think your CE_cod populating loop should be closer to this.

  Dim CE_cod As Variant
  ReDim CE_cod(LBound(rn) to UBound(rn))
  For w = LBound(rn) to UBound(rn)
      'if you want to put a redim here, use Preserve
      'ReDim Preserve CE_cod(w)
      CE_cod(w) = Workbooks("raw_data.xlsm").Worksheets("CE_List").Range(rn(w))
  Next w

The rn array is populated from a Split operation and as such, it is a zero-based, 1-D array.

Additionally, you shouldn't be using ReDim inside a loop without the Preserve parameter if you want to retain your values. In any event, there is no need to ReDim inside the loop since you know the target boundaries beforehand.

Collectives™ on Stack Overflow

Excel VBA Join function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related