counting the number of cell formats in a workbook

I'm currently facing the EXCEL error "number of different cell formats
too high" (it is a raw translation of french text, so maybe it's not
the same message in english)

I know that the maximum number of different cell formats is about 4000
per workbook.

So my point is  : How to trace the evolution of the current number of
different cell formats in my active workbook? Which property of which
object will give me this figure?

Thank you
J�r�me


-- 
jérome Yacc
------------------------------------------------------------------------
jérome Yacc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20785
View this thread: http://www.excelforum.com/showthread.php?threadid=385169

0
7/7/2005 7:11:12 AM
excel 39879 articles. 2 followers. Follow

3 Replies
341 Views

Similar Articles

[PageSpeed] 19

Hi Jerome,

> So my point is  : How to trace the evolution of the current number of
> different cell formats in my active workbook? Which property of which
> object will give me this figure?

I am not awaare of any such property.

> I'm currently facing the EXCEL error "number of different cell formats
> too high" (it is a raw translation of french text, so maybe it's not
> the same message in english)

To resolve your immediate problem,  you could try code posted by Leo Heuser:

'=============================>>
Sub DeleteUnusedCustomNumberFormat�s()
'leo.heu...@get2net.dk, May 6. 2001
'Version 1.01
    Dim Buffer As Object
    Dim Sh As Object
    Dim SaveFormat As Variant
    Dim fFormat As Variant
    Dim nFormat() As Variant
    Dim xFormat As Long
    Dim Counter As Long
    Dim Counter1 As Long
    Dim Counter2 As Long
    Dim StartRow As Long
    Dim EndRow As Long
    Dim pPresent As Boolean
    Dim NumberOfFormats As Long
    Dim Answer
    Dim Cell As Object
    Dim DataStart As Long
    Dim DataEnd As Long
    Dim AnswerText As String
    Dim ActWorkbookName As String
    Dim BufferWorkbookName As String


    NumberOfFormats = 1000
    StartRow = 3 ' Do not alter this value
    EndRow = 16384 ' For Excel 97 and 2000 set EndRow to 65536


ReDim nFormat(0 To NumberOfFormats)


    AnswerText = "Do you want to delete unused custom formats from the
workbook?"
    AnswerText = AnswerText & Chr(10) & "To get a list of used and unused
formats only, choose No."
    Answer = MsgBox(AnswerText, 259)
    If Answer = vbCancel Then GoTo Finito


    On Error GoTo Finito
    ActWorkbookName = ActiveWorkbook.Name
    Workbooks.Add
    BufferWorkbookName = ActiveWorkbook.Name


    Set Buffer = Workbooks(BufferWorkbookName).�ActiveSheet.Range("A3")
    nFormat(0) = Buffer.NumberFormatLocal
    Buffer.NumberFormat = "@"
    Buffer.Value = nFormat(0)


    Workbooks(ActWorkbookName).Act�ivate


    Counter = 1
    Do
        SaveFormat = Buffer.Value
        DoEvents
        SendKeys "{TAB 3}"
        For Counter1 = 1 To Counter
            SendKeys "{DOWN}"
        Next Counter1
        SendKeys "+{TAB}{HOME}'{HOME}+{END}^C{T�AB 4}{ENTER}"
        Application.Dialogs(xlDialogFo�rmatNumber).Show nFormat(0)
        ActiveSheet.Paste Destination:=Buffer
        Buffer.Value = Mid(Buffer.Value, 2)
        nFormat(Counter) = Buffer.Value
        Counter = Counter + 1
    Loop Until nFormat(Counter - 1) = SaveFormat


ReDim Preserve nFormat(0 To Counter - 2)


    Workbooks(BufferWorkbookName).�Activate


    Range("A1").Value = "Custom formats"
    Range("B1").Value = "Formats used in workbook"
    Range("C1").Value = "Formats not used"
    Range("A1:C1").Font.Bold = True


    For Counter = 0 To UBound(nFormat)
        Cells(StartRow, 1).Offset(Counter, 0).NumberFormatLocal =
nFormat(Counter)
        Cells(StartRow, 1).Offset(Counter, 0).Value = nFormat(Counter)
    Next Counter


    Counter = 0
    For Each Sh In Workbooks(ActWorkbookName).Wor�ksheets
        For Each Cell In Sh.UsedRange.Cells
            fFormat = Cell.NumberFormatLocal
            If Application.WorksheetFunction.�CountIf(Range(Cells(StartRow,
2), Cells(EndRow, 2)), fFormat) = 0 Then
                Cells(StartRow, 2).Offset(Counter, 0).NumberFormatLocal =
fFormat
                Cells(StartRow, 2).Offset(Counter, 0).Value = fFormat
                Counter = Counter + 1
            End If
        Next Cell
    Next Sh


    xFormat = Range(Cells(StartRow, 2), Cells(EndRow, 2)).Find("").Row - 2
    Counter2 = 0
    For Counter = 0 To UBound(nFormat)
        pPresent = False
        For Counter1 = 1 To xFormat
            If nFormat(Counter) = Cells(StartRow, 2).Offset(Counter1,
0).NumberFormatLocal Then
                pPresent = True
            End If
        Next Counter1
        If pPresent = False Then
            Cells(StartRow, 3).Offset(Counter2, 0).NumberFormatLocal =
nFormat(Counter)
            Cells(StartRow, 3).Offset(Counter2, 0).Value = nFormat(Counter)
            Counter2 = Counter2 + 1
        End If
    Next Counter
    With ActiveSheet.Columns("A:C")
        .AutoFit
        .HorizontalAlignment = xlLeft
    End With
    If Answer = vbYes Then
        DataStart = Range(Cells(1, 3), Cells(EndRow, 3)).Find("").Row + 1
        DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find("").Row - 1
        On Error Resume Next
        For Each Cell In Range(Cells(DataStart, 3), Cells(DataEnd, 3)).Cells
            Workbooks(ActWorkbookName).Del�eteNumberFormat
(Cell.NumberFormat)
        Next Cell
    End If
Finito:
    Set Cell = Nothing
    Set Sh = Nothing
    Set Buffer = Nothing
End Sub


'<<=============================

---
Regards,
Norman


"jérome Yacc" <jrome.Yacc.1rsi7f_1120723545.7275@excelforum-nospam.com> 
wrote in message 
news:jrome.Yacc.1rsi7f_1120723545.7275@excelforum-nospam.com...
>
> I'm currently facing the EXCEL error "number of different cell formats
> too high" (it is a raw translation of french text, so maybe it's not
> the same message in english)
>
> I know that the maximum number of different cell formats is about 4000
> per workbook.
>
> So my point is  : How to trace the evolution of the current number of
> different cell formats in my active workbook? Which property of which
> object will give me this figure?
>
> Thank you
> J�r�me
>
>
> -- 
> jérome Yacc
> ------------------------------------------------------------------------
> jérome Yacc's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=20785
> View this thread: http://www.excelforum.com/showthread.php?threadid=385169
> 


0
normanjones (1047)
7/7/2005 10:13:10 AM
Hi Jerome,

To add, if you are not familiar with macros, you may wish to visit David 
McRitchie's site and look at his 'Getting Started With Macros And User 
Defined Functions' page:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

---
Regards,
Norman




0
normanjones (1047)
7/7/2005 10:18:47 AM
Hello, Norman

Thank you for your help.
Unfortunately, the use of the proposed macro will not help, as , to be
more precise, the error occurs when executing a macro which aims, at
inserting into a workbook sheets from other workbook, after
pre-processing them.
So it seems to me not conceavable to clean up the workbook after each
insertion. Moreover, the point is that I do not know if the problem
arises from polluting unused cells or from "normal" reasons, that is
because in my pre-processing, I define for (large) range of cells
conditional formats. 

One possible explanation for my problem is that EXCEL accounts for a
new cell format if for instance the text in the condition attached to
the conditional format varies.
Indeed, in my condition, I use a VBA macro to test if the cell contains
a formula, so the text of this condition is something like :
"=isFormula($A$1)", when attached to $A$1 cell, where isFormula(aRange)
returns true if aRange contains a formula.
So, for each addressed cell, the text of the condition varies with the
cell reference.

Another possible explanation for my problem is that EXCEL manages cell
formats relative to a sheet, even when the same formats applies between
two sheets. I raises this point because I've pointed out that at each
sheet insertion, the number of format styles increases,  because these
styles' names are qualified by the sheet name, even if completely
identical. Format styles are not the same as cell formats, but  if it
is done this way for the styles, maybe it is the same for cell
formats.

This is why I wanted to trace, between each insertion, the evolution of
the number of cell formats the receiving workbook has to manage.

Best regards
J�r�me


-- 
jérome Yacc
------------------------------------------------------------------------
jérome Yacc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20785
View this thread: http://www.excelforum.com/showthread.php?threadid=385169

0
7/7/2005 12:44:21 PM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

Paragraph formating jumps to defaults.
Hi, We are using Office/Outlook 2007 We have an email signature script that is run when users login, it´s working ok except for two things; If the default paragraph settings in Word 2007 for a user matches those in the signature script, the signature will use the "Base defaults" of Word 2007, that is SpaceAfter = 10 and LineSpacing = "Multiple" with a value of 1,15. So if I change the script to use SpaceAfter=0 and the user has set his/her Word 2007 to use SpaceAfter=0, the result will be SpaceAfter=10. If the script is set to SpaceAfter=0 and the us...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Where is the lasso feature in 2008? (was in formatting palette in 2004)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, <br><br>In Excel 2004 in the formatting palette in image, there are different selection marquees and lasso's. Where are these features in 2008 Excel? I can't find them anywhere. <br><br>thnx I just found my answer, the &quot;genius's&quot; at Microsoft / MAC Office decided to kill off all these editing features... <br><br>Over $200 to upgrade to a product with LESS features... I don't think so... unbelievable, I will continue to use 2004 because 2008...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

formatting auto replies
Does anyone know of a method that allows outlook 2k2 to format automatic replies regardless of the format of the original message? I setup this automatic response rule that replies with a template, but problem is, when replying to http clients (hotmail or yahoo), the message at the receiver's end has words totally misplaced, all over the page. Any suggestions would be appreciated. ...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041 View this thread: http://www.excelforum.com/showthread.php?th...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

limit the number of outgoing messages
Hi, who can I limit the number of concurrent outgoing messages? for example: if I have a queue of 1000 messages, how can I force exchange to send only 50 messages at a time? ...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Varying format
Is there any way to format a cell differently depending on the number of decimal places typed in? What I need is a minimum of 2dp but 3 if the typed entry matches. eg 1.2 appears as 1.20 and 1.234 appears as typed. TIA Ian Hi! Try formatting the cell with a custom number format such as 0.00## Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforum.com/showthread.php?threadid=27494 "AlfD" <AlfD.1f5q6o@excelforum-nospam.c...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Setting appointment start & end time increments format
In Outlook 2002, I can easily set the Day View to display the time increments in 15 minute blocks, but on the Appointments form, the Start and End Times available on the drop-down menus, respectively, are in 30 minute increment blocks only. How can I set the format for the appointment page to have the Start and End Times for appointments have increment time blocks of 15 minutes? Thanks! My direct email address is: solutioncounselor@comcast.net . If you set your appointments in 15 minute blocks Outlook will start to offer that. It takes a few uses to work. -- Nikki Peterson [MVP ...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

difficult format
I want to format a code like this: ##.0001.## so then i push it down and gives me 0002 and so on, the other ## r numbers. The problem is that when i do that, it sums in the last two numbers instead of what i want, so i ask if it is possible to format like this: (text)##.(number)0001.(text)## so it guives me the sum of number then do what i want. if it is possible how ca i do that? Thanks You can try this, starting in row 1: ="##."&TEXT(ROW(),"0000")&".##" You'll have to adjust the "Row()" number when starting in any other row. For example,...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...