writing values of a range inside a range
 

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

writing values of a range inside a range
 

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.

--
Regards,
Tom Ogilvy




"sloth" wrote in message
oups.com...
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

writing values of a range inside a range
 

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?

writing values of a range inside a range
 

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.

--
Regards,
Tom Ogilvy

"sloth" wrote in message
oups.com...
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?