No cells found - SpecialCells Constants, 23 - don't understand

Hello,

I have a code to populate visible cells which works fine on its own (eg. If I auto-filter manually & then run it) but its not working within this code I wrote to automate my filtering & I don't know why.

Code is below.The De-Bugger highlighted the line "Set r2 = r1.SpecialCells(xlCellTypeConstants, 23)" and said that No Cells Found. But on the spreadsheet the Macro has successfully filtered for those cells and there are cells waiting to be populated. On my first filter (=4), all cells in column E are Blank - I don't know whether that has anything to do with it, but the Labelling code was written to handle both blank & non-blank cells.

The structure I was trying to put together is Insert Row(once only)/Auto-Filter1/Label1/Undo-FIlter1/Auto-Filter2/Label2.

My searching so far has not found the solution to why this code does not work - would greatly appreciate someone's advice. I am learning alot through this process.

Please Login or Register  to view this content.

Thanks,
Trent

Attached is my before and after. My macro inserts a blank column to be located as column E & filters column AI for =4, populates column E with "Pattern 1", undo filter, then filter column AJ for =5, then populate column E with "Pattern 2". Here is the macro code I was trying to use but its not working.
NOTE : My actual filters are more complicated - I am just trying to get the structure of the VBA correct andI can do the rest.

Advice on what is wrong with this would be much appreciated.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

constant means non blank and you are trying to assign blank columns E range in to it, hence for the error

Hello,

Maybe try open my workbooks now. I had to change version.

Some cells in column E will be Non-Blank after "Pattern 1" label is populated there & my section of code being used to populate those Pattern names is meant to accommodate where one record will be both (eg " Pattern 1//Pattern 2" - how do I keep that functionality but avoid this Range definition conflict ?

Originally Posted by davesexcel

I can't open your workbooks.

Hi Dave,
Interesting!
I have downloaded office 365 with office 2013 in to PC, but had excel 2010 already installed. All my existing excel books are turned in to excel 2013 version.
I am also unable to download the attached book, but the error message was something to do with version compatibility.
I managed to download the same attached on a different PC which has excel 2003.
I am now worried that there may be compatibility issues.
I still have excel 2010 on my tab top.
Do you know if I can still open excel 2003 files in 2013?

Ohm,
I did not even notice it was xlsx. I managed to open the last attached in excel 2013.

Hi Rory,
I get a weekly e-mail from a client. The client attached two sheets, one with xls extension and the other one as xlsx. I can open both files from excel 2010. I can not open the xlsx in 2003, but can open xls.
I do not even know how this xlsx file is created.
What worried me is the compatibility issue. It is all good for MS to realise new versions every 2 years, but remain silent when people are locked-in version and too late to do anything.

Hi all,

Grateful for the attention you've provided my thread here.

Without the Before, After files opening I should just say that my above code was just trying to run this sequence : Insert column E(once only)/Auto-Filter1/Label1/Undo-FIlter1/Auto-Filter2/Label2(where cell is already Label1, it becomes "Label1/Label2"). I am needing a macro because in reality my Auto-filters are extremely complex.

I was thinking my macro code (copied below) would work because the sections of code that populate column E seems to work when I have filtered manually. All I have done is Recorded a macro and copied/pasted it all together.

If I can't use that line r1.SpecialCells(xlCellTypeConstants, 23) because non-blank doesn't match with the initial state of column E (which is blank to begin with) could someone please advise what my alternative is ? Column E certainly begins Blank, but after "Pattern 1" is populated, when its time for "Pattern 2" to be populated, some cells are both and its meant to show "Pattern 1/Pattern 2".

Again, I am mostly learning through Recording macros and this forum but knowing the correct syntax to define is something I largely get from reading posts, so anyone's help on this would be greatly appreciated. Thanks very much,Trent.

Just so you don't need to scroll all the way up again, my code is as follows:

Please Login or Register  to view this content.

My first code using Else wasnt filling my whole column and i thought it was a memory issue but when i used Your code (using SpecialCells) which also caters for labelling when the cell has already been labelled by Pattern 1 (eg. "Pattern 1/Pattern2") worked so Ive just I inserted your code into a sequence that includes my auto-filtering. Column E is blank to begin with but depending in the auto-filter could also be only blank visible cells later also - just need a labelling code that checks the visible cells and works like an IF ELSE....but that original code was stopping halfway for no reason(my spreadsheet was20000 rows and 60 columns and I concluded it may have been memory.

Is there an Else line I can incorporate into a SpecialCells instruction or do they not work like that?

Thanks for your consideration of my problem

The reason I need to make a distinction is that after each Pattern is labelled (& I have around 15 Patterns) some records have multiple patterns applying so I need to populate those cells as CellValue&"//Pattern 6" where Cell Value is already Pattern 2 or whatever previous label applied.

I have figured out a solution though not ideal - I type something into E1 as part of the macro and include E1 in the range for SpecialCells so after all patterns are labelled E1 had a lot of text but all the data below had been correctly labelled.

I hope you could see where I was coming from. It's a pity the IF ELSE didn't work but I thought there would have been a more elegant solution.

You've helped me reach an end that is functional enough and I'm very grateful for your time.

Regards
Trent