A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VBA to Concatenate Two Fields of an Array Element



 
 
Thread Tools Display Modes
  #1  
Old June 30th 05, 10:32 PM posted to microsoft.public.excel.programming
Frank & Pam Hayes[_2_]
external usenet poster
 
Posts: 34
Default VBA to Concatenate Two Fields of an Array Element

I am trying to concatenate two fields in a array in order to populate a
third field of the same array. The example is that the array contains a
field that holds the Personal Name and one field that holds the Family Name
for an individual. I would like to concatenate these two into the Full
Name of the individual with a blank between the two, and then place the
concatenated text into a third array field. I could not get Concatenate to
work and Google searches of the group did not reveal any ideas to me. Here
is my code.

Option Explicit

Sub Array_Concatenate()

Dim MyArray As Variant
Dim i As Integer

' Load Array
ReDim MyArray(1 To 5, 1 To 2)
For i = 1 To 5
MyArray(i, 1) = "John"
MyArray(i, 2) = "Smith"
Next

' Redim the Array to add a third element dimension
' using Preserve to keep the data already in the Array
ReDim Preserve MyArray(1 To UBound(MyArray), 1 To 3)

' Concatenate the First Name and Last Name into a Full Name
' and place the full name in the third element of the array
For i = 1 To UBound(MyArray)
'insert logic to make MyArray(i,3) = to the concatenation of
'MyArray(i,1) then a blank space, then MyArray(i,2)
MsgBox i & " = " & MyArray(i, 3)
Next

End Sub

Thanks for any help pointing me in the right direction.

Frank Hayes


Ads
  #2  
Old June 30th 05, 10:50 PM posted to microsoft.public.excel.programming
Doug Glancy
external usenet poster
 
Posts: 770
Default VBA to Concatenate Two Fields of an Array Element

Frank,

This line will do what you want:

MyArray(i, 3) = MyArray(i, 1) & " " & MyArray(i, 2)

hth,

Doug

"Frank & Pam Hayes" > wrote in message
news:wxZwe.8287$dz6.1421@trnddc02...
> I am trying to concatenate two fields in a array in order to populate a
> third field of the same array. The example is that the array contains a
> field that holds the Personal Name and one field that holds the Family

Name
> for an individual. I would like to concatenate these two into the Full
> Name of the individual with a blank between the two, and then place the
> concatenated text into a third array field. I could not get Concatenate

to
> work and Google searches of the group did not reveal any ideas to me.

Here
> is my code.
>
> Option Explicit
>
> Sub Array_Concatenate()
>
> Dim MyArray As Variant
> Dim i As Integer
>
> ' Load Array
> ReDim MyArray(1 To 5, 1 To 2)
> For i = 1 To 5
> MyArray(i, 1) = "John"
> MyArray(i, 2) = "Smith"
> Next
>
> ' Redim the Array to add a third element dimension
> ' using Preserve to keep the data already in the Array
> ReDim Preserve MyArray(1 To UBound(MyArray), 1 To 3)
>
> ' Concatenate the First Name and Last Name into a Full Name
> ' and place the full name in the third element of the array
> For i = 1 To UBound(MyArray)
> 'insert logic to make MyArray(i,3) = to the concatenation of
> 'MyArray(i,1) then a blank space, then MyArray(i,2)
> MsgBox i & " = " & MyArray(i, 3)
> Next
>
> End Sub
>
> Thanks for any help pointing me in the right direction.
>
> Frank Hayes
>
>



  #3  
Old June 30th 05, 10:59 PM posted to microsoft.public.excel.programming
Frank & Pam Hayes[_2_]
external usenet poster
 
Posts: 34
Default VBA to Concatenate Two Fields of an Array Element

Doug,

Thanks for the help. It worked perfectly. I tried a bunch of complicated
things ... I should have tried the simple ones too!

Frank

"Doug Glancy" > wrote in message
...
> Frank,
>
> This line will do what you want:
>
> MyArray(i, 3) = MyArray(i, 1) & " " & MyArray(i, 2)
>
> hth,
>
> Doug
>
> "Frank & Pam Hayes" > wrote in message
> news:wxZwe.8287$dz6.1421@trnddc02...
>> I am trying to concatenate two fields in a array in order to populate a
>> third field of the same array. The example is that the array contains a
>> field that holds the Personal Name and one field that holds the Family

> Name
>> for an individual. I would like to concatenate these two into the Full
>> Name of the individual with a blank between the two, and then place the
>> concatenated text into a third array field. I could not get Concatenate

> to
>> work and Google searches of the group did not reveal any ideas to me.

> Here
>> is my code.
>>
>> Option Explicit
>>
>> Sub Array_Concatenate()
>>
>> Dim MyArray As Variant
>> Dim i As Integer
>>
>> ' Load Array
>> ReDim MyArray(1 To 5, 1 To 2)
>> For i = 1 To 5
>> MyArray(i, 1) = "John"
>> MyArray(i, 2) = "Smith"
>> Next
>>
>> ' Redim the Array to add a third element dimension
>> ' using Preserve to keep the data already in the Array
>> ReDim Preserve MyArray(1 To UBound(MyArray), 1 To 3)
>>
>> ' Concatenate the First Name and Last Name into a Full Name
>> ' and place the full name in the third element of the array
>> For i = 1 To UBound(MyArray)
>> 'insert logic to make MyArray(i,3) = to the concatenation of
>> 'MyArray(i,1) then a blank space, then MyArray(i,2)
>> MsgBox i & " = " & MyArray(i, 3)
>> Next
>>
>> End Sub
>>
>> Thanks for any help pointing me in the right direction.
>>
>> Frank Hayes
>>
>>

>
>



  #4  
Old June 30th 05, 11:18 PM posted to microsoft.public.excel.programming
Doug Glancy
external usenet poster
 
Posts: 770
Default VBA to Concatenate Two Fields of an Array Element

I know how that goes!

Doug

"Frank & Pam Hayes" > wrote in message
news:yXZwe.8288$dz6.1328@trnddc02...
> Doug,
>
> Thanks for the help. It worked perfectly. I tried a bunch of

complicated
> things ... I should have tried the simple ones too!
>
> Frank
>
> "Doug Glancy" > wrote in message
> ...
> > Frank,
> >
> > This line will do what you want:
> >
> > MyArray(i, 3) = MyArray(i, 1) & " " & MyArray(i, 2)
> >
> > hth,
> >
> > Doug
> >
> > "Frank & Pam Hayes" > wrote in message
> > news:wxZwe.8287$dz6.1421@trnddc02...
> >> I am trying to concatenate two fields in a array in order to populate a
> >> third field of the same array. The example is that the array contains

a
> >> field that holds the Personal Name and one field that holds the Family

> > Name
> >> for an individual. I would like to concatenate these two into the

Full
> >> Name of the individual with a blank between the two, and then place the
> >> concatenated text into a third array field. I could not get

Concatenate
> > to
> >> work and Google searches of the group did not reveal any ideas to me.

> > Here
> >> is my code.
> >>
> >> Option Explicit
> >>
> >> Sub Array_Concatenate()
> >>
> >> Dim MyArray As Variant
> >> Dim i As Integer
> >>
> >> ' Load Array
> >> ReDim MyArray(1 To 5, 1 To 2)
> >> For i = 1 To 5
> >> MyArray(i, 1) = "John"
> >> MyArray(i, 2) = "Smith"
> >> Next
> >>
> >> ' Redim the Array to add a third element dimension
> >> ' using Preserve to keep the data already in the Array
> >> ReDim Preserve MyArray(1 To UBound(MyArray), 1 To 3)
> >>
> >> ' Concatenate the First Name and Last Name into a Full Name
> >> ' and place the full name in the third element of the array
> >> For i = 1 To UBound(MyArray)
> >> 'insert logic to make MyArray(i,3) = to the concatenation of
> >> 'MyArray(i,1) then a blank space, then MyArray(i,2)
> >> MsgBox i & " = " & MyArray(i, 3)
> >> Next
> >>
> >> End Sub
> >>
> >> Thanks for any help pointing me in the right direction.
> >>
> >> Frank Hayes
> >>
> >>

> >
> >

>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Array element asingh Excel Worksheet Functions 4 April 12th 10 03:30 PM
Permutations of an array element <> to a value Bruce Excel Worksheet Functions 3 January 31st 06 04:00 PM
Set array element to empty Raul Excel Programming 2 March 2nd 05 03:53 PM
Array element Andrea[_8_] Excel Programming 5 December 7th 04 08:24 PM
deleting array element michael Excel Programming 0 December 18th 03 08:55 PM


All times are GMT +1. The time now is 08:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.