View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Frank & Pam Hayes[_2_] Frank & Pam Hayes[_2_] is offline
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