Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 30th 05, 10:32 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2004
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



  #2   Report Post  
Old June 30th 05, 10:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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:[email protected]..
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   Report Post  
Old June 30th 05, 10:59 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2004
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:[email protected]..
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   Report Post  
Old June 30th 05, 11:18 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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:[email protected]..
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:[email protected]..
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 03:38 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017