Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 -- Best regards Joergen Bondesen "green fox" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 Alan Beban wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 Joergen Bondesen wrote: 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 -- Best regards Joergen Bondesen "green fox" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
green fox wrote:
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 -- Gary "green fox" wrote in message oups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
How about:
MsgBox Join(myarray, " ") NickHK "green fox" wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
Debug.Print Join(myarray, ", ")
Not sure how far back Join goes though (XL97?) Tim "green fox" wrote in message oups.com... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 Gary Keramidas wrote: 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 -- Gary "green fox" wrote in message oups.com... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a small array to a single cell as a string
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 Tim Williams wrote: Debug.Print Join(myarray, ", ") Not sure how far back Join goes though (XL97?) Tim "green fox" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to continue writing in a single cell? | Excel Discussion (Misc queries) | |||
Writing Single Dimension Array to Active Sheet | Excel Programming | |||
having trouble writing string to cell-xl2k | Excel Programming | |||
VBA - Convert my variable range array to single cell string | Excel Programming | |||
"Small" Multi-Dimensional Array Slow Writing To Cell | Excel Programming |