Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errortrap for WorksheetFunction.CountA - "No cells were found"?
Hi,
I have a rng that I want to check if there are any hidden not empty cells (- the visible cells are of no interest.) It work ok if there exist non-empty hidden cells, but I get error when it can't find any cells (some of the code in lines below). OK, fine I thought - then I make an error trap and make GoTo if I get an error ... but I can't make it work and I now I ask: Check rng if hidden cells aren't empty - I CAN'T be the first to need this - can anybody please give me a sugestion how to solve the task? There might be another better approach, or am I on the right track? On Error GoTo FormCheck 'check contants and formulas together If Application.WorksheetFunction.CountA(Union(Range(r ng).SpecialCells(xlCellTypeConstants, 23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))) 0 Then Set rngconform = Union(Range(rng).SpecialCells(xlCellTypeConstants, 23), Range(rng).SpecialCells(xlCellTypeFormulas, 23)) End If ... Other code ... Exit Sub FormCheck: etc /Regards tysk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errortrap for WorksheetFunction.CountA - "No cells were found"?
Dim rngForm as Range, rngConst as Range
Dim rngConForm as Range On error Resume Next set rngForm = rng.SpecialCells(xlFormulas,23) set rngConst = rng.SpecialCells(xlConstants,23) On Error goto 0 if not rngForm is nothing then if rngConst is nothing then set rngconform = rngForm else set rngconform = Union(rngForm, rngConst) end if elseif not rngConform is nothing then set rngconform = rngConst else set rngConform = Nothing End if I didn't see anything in your code about visible or hidden cells. -- Regards, Tom Ogilvy "tskogstrom" wrote: Hi, I have a rng that I want to check if there are any hidden not empty cells (- the visible cells are of no interest.) It work ok if there exist non-empty hidden cells, but I get error when it can't find any cells (some of the code in lines below). OK, fine I thought - then I make an error trap and make GoTo if I get an error ... but I can't make it work and I now I ask: Check rng if hidden cells aren't empty - I CAN'T be the first to need this - can anybody please give me a sugestion how to solve the task? There might be another better approach, or am I on the right track? On Error GoTo FormCheck 'check contants and formulas together If Application.WorksheetFunction.CountA(Union(Range(r ng).SpecialCells(xlCellTypeConstants, 23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))) 0 Then Set rngconform = Union(Range(rng).SpecialCells(xlCellTypeConstants, 23), Range(rng).SpecialCells(xlCellTypeFormulas, 23)) End If ... Other code ... Exit Sub FormCheck: etc /Regards tysk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errortrap for WorksheetFunction.CountA - "No cells were found"?
Thank you for the help,
That is correct, I forgot to enclose the code part regarding hidden cells. /tysk tskogstrom skrev: Hi, I have a rng that I want to check if there are any hidden not empty cells (- the visible cells are of no interest.) It work ok if there exist non-empty hidden cells, but I get error when it can't find any cells (some of the code in lines below). OK, fine I thought - then I make an error trap and make GoTo if I get an error ... but I can't make it work and I now I ask: Check rng if hidden cells aren't empty - I CAN'T be the first to need this - can anybody please give me a sugestion how to solve the task? There might be another better approach, or am I on the right track? On Error GoTo FormCheck 'check contants and formulas together If Application.WorksheetFunction.CountA(Union(Range(r ng).SpecialCells(xlCellTypeConstants, 23), Range(rng).SpecialCells(xlCellTypeFormulas, 23))) 0 Then Set rngconform = Union(Range(rng).SpecialCells(xlCellTypeConstants, 23), Range(rng).SpecialCells(xlCellTypeFormulas, 23)) End If ... Other code ... Exit Sub FormCheck: etc /Regards tysk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Catching "no cells were found" | Excel Programming | |||
Problem using "Find" WorksheetFunction in Excel VBA | Excel Programming | |||
VBA: "Volatile" use of concatenated worksheetfunction expression | Excel Programming | |||
Getting "compile error" "method or data member not found" on reinstall | Excel Programming |