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 |
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 |