Adding values to an already existing array

[earldunning]

Hello,
I have a list of UserIDs that I am grabbing as an array to populate a "To:" box in an email.

I have this all working except I want to add 2 more values to the array that are not in the list.

So what I have which works great is:

Dim vaRecipients As Variant

vaRecipients = Range("CRToIDList").Value  'this is my range for my standard mailing list

But what I want is

vaRecipients = Range("CRToIDList").Value &  Range("E1").Value & Range("E2").Value

I am trying to pick up 2 more userIDs off the current form and add them to the standard list. I do not however want to add them permanently, only for the purpose of emailing. It errors when I try this. How do I add the 2 extra values to the end of the array? Do I need to count how many are in the original list and then increment? It seems like there should be an easy way to just add it to the end regardless.

Thoughts?
Thanks

 

[gauntletxg]

Try something like

Dim myArr() As Variant, iLast As Integer
myArr = Range("A1:A5").Value
myArr = WorksheetFunction.Transpose(myArr)
iLast = UBound(myArr)

ReDim Preserve myArr(1 To iLast + 2)
myArr(iLast + 1) = Range("B1").Value
myArr(iLast + 2) = Range("B2").Value

This reads A1:A5 into the array, then adds B1 and B2.

 

[earldunning]

vaRecipients = Range("CRToIDList").Value
vaRecipients = WorksheetFunction.Transpose(vaRecipients)
iLast = UBound(vaRecipients) + 2
MsgBox (iLast)
ReDim Preserve vaRecipients(1 To iLast + 2)
vaRecipients(iLast - 1) = Range("E1").Value
vaRecipients(iLast) = Range("E2").Value


I think its working!

Thanks!!

 

[gauntletxg]

You're not re-dimensioning the array.

 

[earldunning]

I realized that just after I posted and modified the post. You must have looked at it just after I posted. Your too quick!!!
Check above now. Does it look right?