Identifying cell types in column using .SpecialCells(xlConstants, xlNumbers)
 

Hello,

As part of the work I am doing I am trying to go through the columns i
a spreadsheet and identfiy the cell types usin
.SpecialCells(xlConstants, xlNumbers/xlText etc)

I specify the column to check using the following command:
toBeSpecdName = "my sheet"
set tempWs = worksheets.add

Set tempCellRange = Worksheets(toBeSpecdName).Range(Cells(rowStartPos
colStartPos), Cells(rowEndPos, colStartPos))

where the variables are ints.

If I do the set tempCellRange command before the new worksheet i
created then it works but it fails if I do it afterwards it fail
with:
1004 Application error
and the debugger points at the Range command.

I aim to build a string that contains all the types in the column s
users can go and fix any columnns that have problems.

Anyone got any ideas?

Regards,

Mar

--
Message posted from http://www.ExcelForum.com

Identifying cell types in column using .SpecialCells(xlConstants, xlNumbers)
 

The obvious answer is that at least on of your four xxxPos variables has
an illegal value.

--
Regards,
Tom Ogilvy


"robbinma " wrote in message
...
Hello,

As part of the work I am doing I am trying to go through the columns in
a spreadsheet and identfiy the cell types using
SpecialCells(xlConstants, xlNumbers/xlText etc)

I specify the column to check using the following command:
toBeSpecdName = "my sheet"
set tempWs = worksheets.add

Set tempCellRange = Worksheets(toBeSpecdName).Range(Cells(rowStartPos,
colStartPos), Cells(rowEndPos, colStartPos))

where the variables are ints.

If I do the set tempCellRange command before the new worksheet is
created then it works but it fails if I do it afterwards it fails
with:
1004 Application error
and the debugger points at the Range command.

I aim to build a string that contains all the types in the column so
users can go and fix any columnns that have problems.

Anyone got any ideas?

Regards,

Mark


---
Message posted from http://www.ExcelForum.com/

Identifying cell types in column using .SpecialCells(xlConstants,xlNumbers)
 

Another guess: Your code is behind a worksheet and the unqualified range
references belong to the sheet that owns the code--not the newly added
worksheet:

Instead of:
Set tempCellRange = Worksheets(toBeSpecdName).Range(Cells(rowStartPos, _
colStartPos), Cells(rowEndPos, colStartPos))

Try:

with worksheets(tobespecdname)
set tempcellrange = .range(.cells(rowstartpos,colstartpos), _
.cells(rowendpos,colstartpos))
end with

(watch for typos!)

And notice the dots. That means that thing belongs to the previous With's
object (in this case worksheets(tobespecdname).



"robbinma <" wrote:

Hmm.

The command worked ok before the add worksheet and failed afterwards
without any changes to the variables.

I have just found the varType function and this does what I wanted on a
cell level. I wanted to use the Special Cells on a column but the
problem can be solved at a cell level although it will take a bit
longer to run.

Thanks,

Mark

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson