Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
How to get range name inside fuction | Excel Programming | |||
Selecting a Range inside a range | Excel Programming | |||
Check if a range is inside another | Excel Programming |