Filling Array from Cells on Worksheet

M

Mike R.

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
 
G

gocush

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
 
O

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top