How do you repeat a function in a formula

[Hatman]

I want to copy over text from various fields into a single field.
Here is the only solution (my limited time and my limited programing savy) I
have found so far. Is there a way to minimise or shorten this
equation/formula:

=A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&"
"&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&"
"&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27

In essense, I want to tell a cell to capture the info in a3 through a27 with
a space inbetween data/value returned.

Thanks

[Gary''s Student]

Try this small macro:


Function glue_it(R As Range) As String
Dim rr As Range
glue_it = ""
For Each rr In R
glue_it = glue_it & " " & rr.Value
Next
End Function

Use it as =glue_it(A3:A27)
--
Gary''s Student


"Hatman" wrote:

> I want to copy over text from various fields into a single field.
> Here is the only solution (my limited time and my limited programing savy) I
> have found so far. Is there a way to minimise or shorten this
> equation/formula:
>
> =A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&"
> "&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&"
> "&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27
>
> In essense, I want to tell a cell to capture the info in a3 through a27 with
> a space inbetween data/value returned.
>
> Thanks
>

[Ken Johnson]

Hi Gary''s Student,
I sometimes need to do that, so I copied your glue_it function into my
personal.xls.
As it stands the result has a leading space so I've changed it to fix
that and I've also made it volatile...

Function glue_it(R As Range) As String
Application.Volatile
Dim rr As Range
glue_it = ""
For Each rr In R
glue_it = glue_it & " " & rr.Value
Next
glue_it = Application.Trim(glue_it)
End Function

Ken Johnson

[Hatman]

Thanks a bunch, this worked.
Now I was a pain in High School for my Math teachers, I always wanted to
know the rhyme and reason behind formulas, and such I shall continue being so
here in order to learn.
Can you be so kind as to walk me through the logic (the why's of the
equation) of your function.
I just like to be able to understand what it does.
Let me take a crack at it.
Function glue_it : This names the function
THe rest is mumbo jumbo to me.

"Gary''s Student" wrote:

> Try this small macro:
>
>
> Function glue_it(R As Range) As String
> Dim rr As Range
> glue_it = ""
> For Each rr In R
> glue_it = glue_it & " " & rr.Value
> Next
> End Function
>
> Use it as =glue_it(A3:A27)

[Dana DeLouis]

>> In essense, I want to tell a cell to capture the info in a3 through a27
>> with
>> a space in-between data/value returned.

If your data is just one column, here's another option...

Function GlueIt(rng) As String
GlueIt = Join(WorksheetFunction.Transpose(rng), Space(1))
End Function

--
HTH. :>)

Dana DeLouis
Windows XP, Office 2003



"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> Try this small macro:
>
>
> Function glue_it(R As Range) As String
> Dim rr As Range
> glue_it = ""
> For Each rr In R
> glue_it = glue_it & " " & rr.Value
> Next
> End Function
>
> Use it as =glue_it(A3:A27)
> --
> Gary''s Student
>
>
> "Hatman" wrote:
>
>> I want to copy over text from various fields into a single field.
>> Here is the only solution (my limited time and my limited programing
>> savy) I
>> have found so far. Is there a way to minimise or shorten this
>> equation/formula:
>>
>> =A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&"
>> "&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&"
>> "&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27
>>
>> In essense, I want to tell a cell to capture the info in a3 through a27
>> with
>> a space inbetween data/value returned.
>>
>> Thanks
>>