View Single Post
  #1   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

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