.SpecialCells, xlCellTypeConstants, and a null value
 

Hi All,

SVCNaRng below contains two cells with constant values,
AND 1 cell with a null value, and I can't figure why.

The 1st code snippet below has worked in other procs.
uPrm is a record with row and column# parms and cell
addresses, these values are good.

MiscRng is 1 column, rows 17 thru 33.
Some cells are locked and some are not.
NO locked cells have data.

SVCNaRng correctly contains the cells in rows
17 and 18 with data(each has "Last,First" string
name data). It also contains the row 21 cell which
is null.

When I built my own loop, 2nd code snippet, I get the
results I expect ???

So, what's up with specialcells and constants ?
Thanks


'snippet 1

Set MiscRng = Ws.Range(Ws.Cells(uPrm.ColHdrRow + 1, uPrm.uCol.SubNa), _
Ws.Cells(uPrm.uRng.PaEndRow.Value, uPrm.uCol.SubNa))
Call unprotect_std(Ws)
On Error Resume Next
Set SVCNaRng = MiscRng.SpecialCells(xlCellTypeConstants)
Err.Clear

If Not SVCNaRng is Nothing Then .....


'snippet 2, workaround loop below works FINE,
Set SVCNaRng = Nothing
For Each OneCell In MiscRng
If OneCell.Value < "" Then
If SVCNaRng Is Nothing Then
Set SVCNaRng = OneCell
Else
Set SVCNaRng = Union(SVCNaRng, OneCell)
End If
End If
Next OneCell

--
Neal Z

.SpecialCells, xlCellTypeConstants, and a null value
 

Just a guess, but probably the culprit is th unprotect_std macro.


"Neal Zimm" wrote in message
...
Hi All,

SVCNaRng below contains two cells with constant values,
AND 1 cell with a null value, and I can't figure why.

The 1st code snippet below has worked in other procs.
uPrm is a record with row and column# parms and cell
addresses, these values are good.

MiscRng is 1 column, rows 17 thru 33.
Some cells are locked and some are not.
NO locked cells have data.

SVCNaRng correctly contains the cells in rows
17 and 18 with data(each has "Last,First" string
name data). It also contains the row 21 cell which
is null.

When I built my own loop, 2nd code snippet, I get the
results I expect ???

So, what's up with specialcells and constants ?
Thanks


'snippet 1

Set MiscRng = Ws.Range(Ws.Cells(uPrm.ColHdrRow + 1, uPrm.uCol.SubNa), _
Ws.Cells(uPrm.uRng.PaEndRow.Value, uPrm.uCol.SubNa))
Call unprotect_std(Ws)
On Error Resume Next
Set SVCNaRng = MiscRng.SpecialCells(xlCellTypeConstants)
Err.Clear

If Not SVCNaRng is Nothing Then .....


'snippet 2, workaround loop below works FINE,
Set SVCNaRng = Nothing
For Each OneCell In MiscRng
If OneCell.Value < "" Then
If SVCNaRng Is Nothing Then
Set SVCNaRng = OneCell
Else
Set SVCNaRng = Union(SVCNaRng, OneCell)
End If
End If
Next OneCell

--
Neal Z

.SpecialCells, xlCellTypeConstants, and a null value
 

Kind of a bad guess, JLG, the reason it's there is that I got a 1004 error
without it saying the specialcells needed an UNprotected sheet.
Thanks.
--
Neal Z


"JLGWhiz" wrote:

Just a guess, but probably the culprit is th unprotect_std macro.


"Neal Zimm" wrote in message
...
Hi All,

SVCNaRng below contains two cells with constant values,
AND 1 cell with a null value, and I can't figure why.

The 1st code snippet below has worked in other procs.
uPrm is a record with row and column# parms and cell
addresses, these values are good.

MiscRng is 1 column, rows 17 thru 33.
Some cells are locked and some are not.
NO locked cells have data.

SVCNaRng correctly contains the cells in rows
17 and 18 with data(each has "Last,First" string
name data). It also contains the row 21 cell which
is null.

When I built my own loop, 2nd code snippet, I get the
results I expect ???

So, what's up with specialcells and constants ?
Thanks


'snippet 1

Set MiscRng = Ws.Range(Ws.Cells(uPrm.ColHdrRow + 1, uPrm.uCol.SubNa), _
Ws.Cells(uPrm.uRng.PaEndRow.Value, uPrm.uCol.SubNa))
Call unprotect_std(Ws)
On Error Resume Next
Set SVCNaRng = MiscRng.SpecialCells(xlCellTypeConstants)
Err.Clear

If Not SVCNaRng is Nothing Then .....


'snippet 2, workaround loop below works FINE,
Set SVCNaRng = Nothing
For Each OneCell In MiscRng
If OneCell.Value < "" Then
If SVCNaRng Is Nothing Then
Set SVCNaRng = OneCell
Else
Set SVCNaRng = Union(SVCNaRng, OneCell)
End If
End If
Next OneCell

--
Neal Z


.