writing values of a range inside a range

[sloth]

On my spreadsheet I have a dynamic named range (OutputNames) which has
as values the names of other defined names in my worksheet. I want to
loop thru OutputNames and for each defined name, write all its contents
in a column. Below is the code I've written so far, but it doesn't
seem to work. All it does is write the names of the defined names in
OutputNames, where what I really want it to do is print the values.

So for example, if OutputNames had two defined names, Revenue and
Expense. And Revenue had values 10, 20, 30, and Expense had values
1,2,3, I would want the sub routine when called to write:
10
20
30
1
2
3


This is the first piece of code I've tried to write, so please any help
is appreciated:


Dim rngOutputNames as Range, rngName as Range, oCell as Range
Dim r as Integer, c as Integer

Set rngOutputNames = Range(Application.Names!OutputNames)
Range("B5").Select

For Each rngName In rngOutputNames

For Each oCell In rngName
ActiveCell.Offset(r, c).Value = oCell
r = r + 1
Next

Next

 

[Tom Ogilvy]

Sub AABB()
Dim rng As Range, nm As Name
Dim rng1 As Range, cell As Range
Dim rw As Long
Set rng = Range("OutputNames")
For Each nm In ThisWorkbook.Names
If LCase(nm.Name) <> "outputnames" Then
Set rng1 = Nothing
On Error Resume Next
Set rng1 = nm.RefersToRange
On Error GoTo 0
If Not rng1 Is Nothing Then
If rng1.Parent.Name = rng.Parent.Name Then
If Not Intersect(rng1, rng) Is Nothing Then
For Each cell In rng1
rw = rw + 1
Worksheets("AA").Cells(rw, _
1).Value = cell.Value
Next
End If
End If
End If
End If
Next

End Sub

 

[electronicmailmessage]

Very helpful, thanks. I understand what all the code means, but could
you explain the reasoning behind the following section of code:

If rng1.Parent.Name = rng.Parent.Name Then
If Not Intersect(rng1,rng) is Nothing Then

 

[Tom Ogilvy]

It determines if the named range being look at is within the OutputNames
named range.

Before attempting an intersection, which will give the answer, one must
insure that the named ranges are located on the same worksheet.

 

[sloth]

Tom,

I used the union method to create a range with all the values I want to
write to output. Everything worked fine in my test workbook, but when
I brought it into my real workbook everything blew up.

I keep getting a union error.

Is there a problem with trying to create a range based on union of
ranges from different worksheets? My ranges are on multiple sheets.
If so, is there a work around that you know of?



Here's my code (Note that ranges in the Union are on different sheets):


Sub NameOutput(MaxScenId As Integer, Optional OutputWks As String)

Dim rng1 As Range, rng2 As Range
Dim cell As Range
Dim rngScenId As Range
Dim rngScenData As Range
Dim nm As Name
Dim sOutputWks As String, sDataAddress As String, sDataCatAddress As
String

Dim i As Integer, r As Integer, c As Integer, p As Integer

Set rng1 = Union(Range(Application.Names!Revenue),
Range(Application.Names!COGS), _
Range(Application.Names!EBIT))

sOutputWks = OutputWks

i = 0
c = 1
r = 1

Set rngScenId = Range(Application.Names("Scenario.Input"))

Do While i <= MaxScenId
rngScenId.Value = i
For Each nm In ThisWorkbook.Names
Set rng2 = Nothing
On Error Resume Next
Set rng2 = nm.RefersToRange
On Error GoTo 0
If LCase(nm.Name) = "scendata" Then
nm.Delete
ElseIf LCase(nm.Name) = "scencatdata" Then
nm.Delete
Else
If Not rng2 Is Nothing Then
If Not Intersect(rng1, rng2) Is Nothing Then
If c = 1 Then
p = 0
'write row headings
For Each cell In rng2
p = p + 1
r = r + 1
Worksheets("Sen2").Cells(r, 1).Value = nm.Name
& p
Next
Else
'write values
For Each cell In rng2
r = r + 1
Worksheets("Sen2").Cells(r, c).Value =
cell.Value
Next
End If
End If
End If
End If
Next

'write column headings
Worksheets("Sen2").Cells(1, c).Value = i

'increment scenario id and column, reset row
c = c + 1
r = 1
i = i + 1

Loop

Worksheets("Sen2").Range("A1").Activate

sDataCatAddress = rngScenData.Rows(1).Address
ThisWorkbook.Names.Add Name:="ScenData", RefersTo:="=Sen2!" &
sDataAddress

Set rngScenData = Range(Application.Names!ScenData)
sDataAddress = ActiveCell.CurrentRegion.Address
ThisWorkbook.Names.Add Name:="ScenCatData", RefersTo:="=Sen2!" &
sDataCatAddress


End Sub

 

[Tom Ogilvy]

A range has a parent. The sheet on which it is located. since the result
of a union is a range, then you can't union ranges on different sheets.

I see you try to intersect later in your routine. You will get an error if
you try to intersect with ranges on different sheets. You need to check
and see if they are on the same sheet first. But now I am starting to
repeat myself.

 

[sloth]

Thanks for letting me know about not being able to use intersect and
union on ranges from different sheets.

Let's say I have a defined range called OutputNames. This range is
made up of strings corresponding to other ranges in the workbook (not
confined to one sheet). Do you know how I can use OutputNames in a
loop to write the values of the other names referenced by its name as a
string?

For example:

Defined Name: OutputNames
Range: A1-A4 on the Output sheet
A1: Revenue
A2: Opex
A3: EBIT
A4: Volume

Each of the above is also a range.
Defined Name: Revenue
Range: A1-A5 on the Revenue Sheet
A1: 10
A2: 12
A3: 14
A4: 15
A5: 17

Defined Name: Opex
Range: A1-A5 on the Opex Sheet
A1: 5
A2: 6
A3: 7
A4: 8
etc.


So I want the code to write:
10
12
14
15
17
5
6
7
8
etc....

I would think something like:

'to create ranges
For Each i in OutputNames
Dim rngi as Range
Set rngi = Range(Application.Names(OutputNames(i).Value)
Next i

And then loop through each newly created range writing the values.

But this didn't work. Any thoughts?

 

[Tom Ogilvy]

Sub ABC()
Dim cell as Range, cell1 as Range
Dim rng1 as Range, rw as Long
rw = 0
for each cell in Range("outputnames")
set rng1 = range(cell.value)
for each cell1 in rng1
rw = rw + 1
worksheets("Sheet1").Cells(rw,1).Value = cell.Value
Next cell1
Next cell
End Sub


This code should be placed in a general/standard module to insure success.