writing a small array to a single cell as a string

[green fox]

I've been knocking my head against the desk for a few days about
this... probably due more to my inexperience than the difficulty of the
problem. I have a small (variable size )array and I want to write it to
a single cell as a string. Like this
10 12 10 12

I'm seeing lot's of things about turning stuff into arrays, but haven't
found anything I can adapt to going the other way. Here's my code:

Sub Tester3()

Dim aStr As String
Dim myarray(3)
myarray = Array("10", "12", "10", "12")
Dim i As Variant
For i = LBound(myarray) To UBound(myarray)
sStr = myarray(i) & ", " & IIf(aStr = "", "", ", ") & i.Value

Next i

Debug.Print (aStr)

I got the sStr - myarray... from something T. Ogilvy suggested for a
different, but to me similar problem. I get an error saying I need an
object. I know I need to concatenate the values, but what am I missing?
(besides decent knowledge;-)

a.j. fox

 

[Joergen Bondesen]

Hi green fox

Try this, please


Option Explicit

Sub Tester3()

Dim myarray() As Variant
Dim sStr As String
Dim i As Long

myarray = Array(10, 11, 12, 13)

For i = LBound(myarray) To UBound(myarray)
If sStr = vbNullString Then
sStr = myarray(i)
Else
sStr = sStr & ", " & myarray(i)
End If
Next i
Debug.Print (sStr)
End Sub

 

[Alan Beban]

Sub Tester3()

Dim aStr As String
Dim myarray()
myarray = Array("10", "12", "10", "12")
Dim i As Variant
aStr = myarray(1)
For i = LBound(myarray) + 1 To UBound(myarray)
aStr = aStr & " " & myarray(i)
Next i

Range("A1").Value = aStr
End Sub

Alan Beban

 

[green fox]

Thanks Alan, I had to change the aStr=myarray(1) to aStr=myarray(0) to
get the result I wanted. Has that something to do with the option base
thing?

I appreciate your help, and I'll sleep better tonight, thanks.

Andy

 

[green fox]

Thanks Joergen, it worked like a charm. It will take me a little while
to understand the logic but I'm happy to take the time. Thanks to you
and Alan for you prompt replies.

Andy

 

[Alan Beban]

Thanks Alan, I had to change the aStr=myarray(1) to aStr=myarray(0) to
get the result I wanted. Has that something to do with the option base
thing?

Yes.

 

[Gary Keramidas]

just curious if this does what you want

Option Explicit
Sub Tester3()
Dim i As Long
Dim myarray() As Variant
myarray = Array("10", "12", "10", "12")

For i = LBound(myarray) To UBound(myarray)
Range("a1").Value = Range("A1").Value & " " & myarray(i)
Debug.Print myarray(i)

Next
End Sub

 

[NickHK]

How about:
MsgBox Join(myarray, " ")

NickHK

 

[Tim Williams]

Debug.Print Join(myarray, ", ")

Not sure how far back Join goes though (>XL97?)
Tim

 

[green fox]

Hi Gary,

No, that didn't work...it was similar to what I had in the first place
the result was four separate numbers, not the for numbers together in
one string separated by spaces.

Andy

 

[green fox]

Slick. That worked like a charm. I stumbled on the join while
scrounging around the group, I didn't get it though. Missed by that
much.

Thanks Tim

andy