Filling Array from Cells on Worksheet
 

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

Filling Array from Cells on Worksheet
 

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

"Mike R." wrote:

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

Filling Array from Cells on Worksheet
 

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

Filling Array from Cells on Worksheet
 

these both worked perfect....thank you!

"OJ" wrote:

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