Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells and error question
I have the following code, where I want to set rnge to all the cells in
column two that have values in them. Dim rnge as Range On Error GoTo nothingIncol2: Set rnge = Worksheets("SaveLoad").Range("B:B").SpecialCells(x lCellTypeConstants) On Error GoTo 0 However, if there is nothing in the B column, it returns a Run-time error: "No Cells where found". Shouldn't the error GoTo catch this? Thanks, -Abe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells and error question
Yes it should. Try removing the colon in the on error goto statement. Just
use On Error GoTo nothingIncol2 ' specialcells code. On Error goto 0 Exit sub NothingIncol2: msgbox "None found End sub -- Regards, Tom Ogilvy "Abe" wrote: I have the following code, where I want to set rnge to all the cells in column two that have values in them. Dim rnge as Range On Error GoTo nothingIncol2: Set rnge = Worksheets("SaveLoad").Range("B:B").SpecialCells(x lCellTypeConstants) On Error GoTo 0 However, if there is nothing in the B column, it returns a Run-time error: "No Cells where found". Shouldn't the error GoTo catch this? Thanks, -Abe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells and error question
I removed the colon, but it still gives me an error.
Anything else? Maybe a different way to check for empty cells? Thanks again Tom, -Abe Tom Ogilvy wrote: Yes it should. Try removing the colon in the on error goto statement. Just use On Error GoTo nothingIncol2 ' specialcells code. On Error goto 0 Exit sub NothingIncol2: msgbox "None found End sub -- Regards, Tom Ogilvy "Abe" wrote: I have the following code, where I want to set rnge to all the cells in column two that have values in them. Dim rnge as Range On Error GoTo nothingIncol2: Set rnge = Worksheets("SaveLoad").Range("B:B").SpecialCells(x lCellTypeConstants) On Error GoTo 0 However, if there is nothing in the B column, it returns a Run-time error: "No Cells where found". Shouldn't the error GoTo catch this? Thanks, -Abe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells and error question
If you just want to count
msgbox Application.CountBlank(Activesheet.usedrange) But I have never had problems with the construct you show. So I suggest you go into the VBE and then tools=Options and in the general tab, make sure you have break on unhandled errors select rather than break on all errors. -- Regards, Tom Ogilvy "Abe" wrote: I removed the colon, but it still gives me an error. Anything else? Maybe a different way to check for empty cells? Thanks again Tom, -Abe Tom Ogilvy wrote: Yes it should. Try removing the colon in the on error goto statement. Just use On Error GoTo nothingIncol2 ' specialcells code. On Error goto 0 Exit sub NothingIncol2: msgbox "None found End sub -- Regards, Tom Ogilvy "Abe" wrote: I have the following code, where I want to set rnge to all the cells in column two that have values in them. Dim rnge as Range On Error GoTo nothingIncol2: Set rnge = Worksheets("SaveLoad").Range("B:B").SpecialCells(x lCellTypeConstants) On Error GoTo 0 However, if there is nothing in the B column, it returns a Run-time error: "No Cells where found". Shouldn't the error GoTo catch this? Thanks, -Abe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
specialcells(xlLastCell) and method global range error | Excel Programming | |||
SpecialCells | Excel Programming | |||
SpecialCells Method Question | Excel Programming | |||
VBA ON ERROR does not work with SPECIALCELLS | Excel Discussion (Misc queries) | |||
Drillthrough MDX ERROR(Error.Number:-21467259) ?(urgent question) | Excel Programming |