writing a small array to a single cell as a string


G

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
 
Ad

Advertisements

J

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
 
A

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
 
G

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
 
G

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
 
A

Alan Beban

green said:
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.
 
Ad

Advertisements

G

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
 
T

Tim Williams

Debug.Print Join(myarray, ", ")

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

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
 
Ad

Advertisements

G

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top