Error using 'Union'
I am getting the following error when trying to set up a Union of multiple
ranges located on the same sheet. Run-time error '91': Object variable or With block variable not set. RO_Wkbk refers the workbook number I am working with. Here is the code: Dim RangeYr10 As Range Dim RangeAllYears As Range With Workbooks(RO_Wkbk).Sheets("10-Year Analysis") '''''error occurs on the next line. RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _ .Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _ .Range("F179:AO196"), .Range("F209:AO226"), ..Range("F239:AO256"), _ .Range("F269:AO286"), .Range("F299:AO316")) RangeYr10 = .Range("F299:AO316") End With I'm use the range later to clear contents based on the color of the cell. Any thoughts? Thanks, -- JNW |
Error using 'Union'
To follow up... I added Application. in front of union and that did not work
either. -- JNW "JNW" wrote: I am getting the following error when trying to set up a Union of multiple ranges located on the same sheet. Run-time error '91': Object variable or With block variable not set. RO_Wkbk refers the workbook number I am working with. Here is the code: Dim RangeYr10 As Range Dim RangeAllYears As Range With Workbooks(RO_Wkbk).Sheets("10-Year Analysis") '''''error occurs on the next line. RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _ .Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _ .Range("F179:AO196"), .Range("F209:AO226"), .Range("F239:AO256"), _ .Range("F269:AO286"), .Range("F299:AO316")) RangeYr10 = .Range("F299:AO316") End With I'm use the range later to clear contents based on the color of the cell. Any thoughts? Thanks, -- JNW |
Error using 'Union'
RangeAllYears =
should be Set RangeAllYears = same for RangeYr10 = -- Regards, Tom Ogilvy "JNW" wrote: I am getting the following error when trying to set up a Union of multiple ranges located on the same sheet. Run-time error '91': Object variable or With block variable not set. RO_Wkbk refers the workbook number I am working with. Here is the code: Dim RangeYr10 As Range Dim RangeAllYears As Range With Workbooks(RO_Wkbk).Sheets("10-Year Analysis") '''''error occurs on the next line. RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _ .Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _ .Range("F179:AO196"), .Range("F209:AO226"), .Range("F239:AO256"), _ .Range("F269:AO286"), .Range("F299:AO316")) RangeYr10 = .Range("F299:AO316") End With I'm use the range later to clear contents based on the color of the cell. Any thoughts? Thanks, -- JNW |
Error using 'Union'
The range variables are objects, so you need to use:
Set RangeAllYears = Union(.. whatever) and Set RangeYr10 = .Range("F299:AO316") JNW wrote: I am getting the following error when trying to set up a Union of multiple ranges located on the same sheet. Run-time error '91': Object variable or With block variable not set. RO_Wkbk refers the workbook number I am working with. Here is the code: Dim RangeYr10 As Range Dim RangeAllYears As Range With Workbooks(RO_Wkbk).Sheets("10-Year Analysis") '''''error occurs on the next line. RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _ .Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _ .Range("F179:AO196"), .Range("F209:AO226"), .Range("F239:AO256"), _ .Range("F269:AO286"), .Range("F299:AO316")) RangeYr10 = .Range("F299:AO316") End With I'm use the range later to clear contents based on the color of the cell. Any thoughts? Thanks, -- JNW |
Error using 'Union'
That did it. Thanks!
-- JNW "Tom Ogilvy" wrote: RangeAllYears = should be Set RangeAllYears = same for RangeYr10 = -- Regards, Tom Ogilvy "JNW" wrote: I am getting the following error when trying to set up a Union of multiple ranges located on the same sheet. Run-time error '91': Object variable or With block variable not set. RO_Wkbk refers the workbook number I am working with. Here is the code: Dim RangeYr10 As Range Dim RangeAllYears As Range With Workbooks(RO_Wkbk).Sheets("10-Year Analysis") '''''error occurs on the next line. RangeAllYears = Union(.Range("F29:AO46"), .Range("F59:AO76"), _ .Range("F89:AO106"), .Range("F119:AO136"), .Range("F149:AO166"), _ .Range("F179:AO196"), .Range("F209:AO226"), .Range("F239:AO256"), _ .Range("F269:AO286"), .Range("F299:AO316")) RangeYr10 = .Range("F299:AO316") End With I'm use the range later to clear contents based on the color of the cell. Any thoughts? Thanks, -- JNW |
All times are GMT +1. The time now is 01:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com