Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Identifying the every nth document in a column | Excel Discussion (Misc queries) | |||
Identifying the row and column of a table value | Excel Discussion (Misc queries) | |||
Needed: Chart that combines clustered column and stacked column types | Charts and Charting in Excel | |||
How do I mix column chart types? | Charts and Charting in Excel |