I am trying to convert a variable array to a string using vba. I have tried 2 methods but non of them work, they both seem to bloc on at the same point.
Dim cell As Range
Dim val As Variant
For Each cell In Range("packing_list[Code]")
val = cell.Value
Next cell
MsgBox Join(val, "//")
and
Dim oSh As Worksheet
Dim CodeRange As Variant
Set oSh = ActiveSheet
CodeRange = oSh.Range("packing_list[Code]").Value
MsgBox Join(CodeRange , "//")
They both error on the MsgBox line. What do I do wrong ?
Thanks
The value you are trying to join is not an array of strings. Join is supposed to be used on arrays
Here is the link to the Microsoft instructions: https://msdn.microsoft.com/en-us/library/b65z3h4h%28v=vs.90%29.aspx
Their example is:
Dim TestItem() As String = {"Pickle", "Pineapple", "Papaya"}
Dim TestShoppingList As String = Join(TestItem, ", ")
You code should look something like:
Dim i As Integer
Dim cell As Range
Dim val() As Variant '() indicate it is an array
i = 0
For Each cell In Range("packing_list[Code]")
ReDim Preserve val(0 to i) As Variant 'must resize array to fit number of items
val(i) = cell.Value 'i is the position of the item in the array
i = i + 1 'increment i to move to next position
Next cell
'Now that you have an array of values (i.e. ("String1", "String2", ...) instead of just "String" you can:
MsgBox Join(val, "//")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With