Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Array element | Excel Worksheet Functions | |||
Permutations of an array element < to a value | Excel Worksheet Functions | |||
Set array element to empty | Excel Programming | |||
Array element | Excel Programming | |||
deleting array element | Excel Programming |