Filling Array from Cells on Worksheet

[Guest]

Greetings,
I am using the Routing function on Excel and I would like to autofill the
routing recipients and route the workbook based on the people that are in a
range of cells.

The Cells that auto fill based on fomula's are A1:A6....some cells may be
blank. I am just unfamiliar with arrays, but here is where I am so far with
the code:

ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = Array(????)
End With
ActiveWorkbook.Route

I think I will need to use a FOR statement, but need a little help. Thank
you very much,
Mike

 

[Guest]

Try this:

dim vArr as Variant
dim Rng as Range

set Rng = .Range("A1:A6")
vArr = Rng.Value

ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = vArr
End With
ActiveWorkbook.Route

 

[OJ]

Hi,
Try this...
Sub trythis()
Dim myArr() As String, rngRecip As Range, rngCell As Range, intInc As
Integer

Set rngRecip = ThisWorkbook.Sheets(1).Range("$A$1:$A$6")

For Each rngCell In rngRecip
With rngCell
If .Value <> "" Then
ReDim Preserve myArr(intInc)
Let myArr(intInc) = CStr(.Value)
End If
End With
Next rngCell

With ThisWorkbook
.HasRoutingSlip = True
.RoutingSlip.Recipients = myArr
.Route
End With

Set rngRecip = Nothing
End Sub

 

[Guest]

these both worked perfect....thank you!