Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drag-filling a formula Array | Excel Discussion (Misc queries) | |||
Filling a few cells based on the info from another worksheet. | Excel Worksheet Functions | |||
help with filling in an array formula | Excel Discussion (Misc queries) | |||
Filling a List Box in Excel from an Array | Excel Discussion (Misc queries) | |||
Filling an array with data | Excel Worksheet Functions |