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

  • Thread starter Thread starter robbinma
  • Start date Start date
R

robbinma

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
 
The obvious answer is that at least on of your four xxxPos variables has
an illegal value.
 
Hmm.

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

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

Thanks,

Mar
 
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).
 
Back
Top