Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of Ranges Failed
Public Sub Workbook_Open()
' This Sub checks each Sheet (1-4), calculates the data range for each, and ' then adds then all to a total Data Range (data ranges declared Global ' as WeekOne, WeekTwo, WeekThree, WeekFour and WeekAll) ' Function Call CHKROW Just checks if the cell range is Blank to let me know ' I have reached the end of the data lines on that sheet. Dim AddRange As Range Dim ChkSheet As Worksheet Dim DataLine As Long For Each Sh In ThisWorkbook.Worksheets If Sh.Name < "Totals" Then DataLine = 0 Worksheets(Sh.Name).Activate Set AddRange = Sheets(Sh.Name).Range(Cells(1, 1), Cells(1, 10)) Do DataLine = DataLine + 1 Set AddRange = Union(AddRange, Sheets(Sh.Name).Range(Cells(DataLine, 1), Cells(DataLine, 10))) If ChkRow(DataLine) = Blank Then Exit Do Loop Select Case ActiveSheet.Index Case 1 Set WeekOne = AddRange Case 2 Set WeekTwo = AddRange Case 3 Set WeekThree = AddRange Case 4 Set WeekFour = AddRange End Select Else Set WeekAll = Union(WeekOne, WeekTwo, WeekThree, WeekFour) End If Next End Sub This all works as I wanted it to, until It gets to "Set WeekAll = Union(WeekOne, WeekTwo, WeekThree, WeekFour)" It give me an error setting the range of the global variable. (all the rest worked just fine) Any one know why? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of Ranges Failed
You can't union ranges on separate sheets.
-- Regards, Tom Ogilvy "Barry Wright" wrote in message ... Public Sub Workbook_Open() ' This Sub checks each Sheet (1-4), calculates the data range for each, and ' then adds then all to a total Data Range (data ranges declared Global ' as WeekOne, WeekTwo, WeekThree, WeekFour and WeekAll) ' Function Call CHKROW Just checks if the cell range is Blank to let me know ' I have reached the end of the data lines on that sheet. Dim AddRange As Range Dim ChkSheet As Worksheet Dim DataLine As Long For Each Sh In ThisWorkbook.Worksheets If Sh.Name < "Totals" Then DataLine = 0 Worksheets(Sh.Name).Activate Set AddRange = Sheets(Sh.Name).Range(Cells(1, 1), Cells(1, 10)) Do DataLine = DataLine + 1 Set AddRange = Union(AddRange, Sheets(Sh.Name).Range(Cells(DataLine, 1), Cells(DataLine, 10))) If ChkRow(DataLine) = Blank Then Exit Do Loop Select Case ActiveSheet.Index Case 1 Set WeekOne = AddRange Case 2 Set WeekTwo = AddRange Case 3 Set WeekThree = AddRange Case 4 Set WeekFour = AddRange End Select Else Set WeekAll = Union(WeekOne, WeekTwo, WeekThree, WeekFour) End If Next End Sub This all works as I wanted it to, until It gets to "Set WeekAll = Union(WeekOne, WeekTwo, WeekThree, WeekFour)" It give me an error setting the range of the global variable. (all the rest worked just fine) Any one know why? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
printing Union of Ranges | Excel Worksheet Functions | |||
union method for non-adjacent ranges | Excel Programming | |||
union method for non-adjacent ranges | Excel Programming | |||
union method for non-adjacent ranges | Excel Programming | |||
VBA union of two ranges | Excel Programming |