Posts by tblondet

tblondet · Apr 23rd 2007

Re: Delete All Names

This is exactly the problem but my problem is that I don't know to what I should rename the corrupted names...

Whatever I try gives me "This name is not valid"....

Here are examples of what I tried (as new names for corrupted names):
R1C1
=R1C1
=AEMS!R1C1
='AEMS'!R1C1
A2
=A2
=AEMSA2
='AEMS'!A2
AEMSA2
'AEMS'!A2
Bobby

.....................

tblondet · Apr 23rd 2007

Re: Delete All Names

Thank you for your answer but the macro breaks at the first n.delete

The error is: "That name is not valid"

And I can't upload the file sorry....

I guess my question is just how to rename corrupted names with the R1C1 reference style using Name Manager...... If anyone is familiar with Name Manager...

Best and thanks again for the help,

Thomas

tblondet · Apr 23rd 2007

Re: Delete All Names

This is better than my first macro as it delete ALL the regular names at once.

Nevertheless, I am still having the other names that name manager finds and still can't delete them.

Best,

Thomas

tblondet · Apr 23rd 2007

Hi,

I have an excel file crashing because of corrupted names.
I used the following macro:

Code

Sub DeleteAllNames()
Dim nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
    If nm = "Print_Titles" Then Resume Next
    If nm = "Print_Areas" Then Resume Next
        nm.Delete
Next nm
On Error GoTo 0
End Sub

I then used the name manager to delete hidden names.

Unfortunately some of the names are corrupted...

Name Manager prompts me to change the reference style to R1C1 to rename those corrupted names... but I tried every combination to rename them, but it won't work.......

Here are examples of what I tried (as new names for corrupted names):
R1C1
=R1C1
=AEMS!R1C1
='AEMS'!R1C1
A2
=A2
=AEMSA2
='AEMS'!A2
AEMSA2
'AEMS'!A2
Bobby............................ Pretty much everything I could think of....

Any ideas ??? Would be great as my entire model crashes and is due soon....

tblondet · Mar 15th 2007

Re: Delete All Names

Roy,

Do you know how to insert two if statements in the code to prevent it from deleting the Print_Area and Print_Titles names?

I am kind of stuck on how to insert a second logical test!

The highlighted part is what's not working - very easy one for you I am sure !

Again thanks,

Thomas

Code

Sub DeleteAllNames()
    Dim nm As Name
    On Error Resume Next
    For Each nm In ThisWorkbook.Names
[B]        If nm <> "Print_Area" Then
        GoTo 0
        End If
        If nm <> Print_Titles" then
        GoTo 0
        End If[/B]
        nm.Delete
    Next nm
    On Error GoTo 0
End Sub

Display More

tblondet · Mar 15th 2007

Re: Delete All Names

Ok Roy, so that's I guess the best we can do! Thank you for your help, always quick and solving issues!

tblondet · Mar 15th 2007

Re: Delete All Names

That's great Roy, it finally worked.
I deleted the rest manually but I still have just few of them that are detected by name manager (around 20).
Especially one in many worksheet that finishes by the letter r... (For example I.!r, II.!r...)

Do you guys have a clue on how to force name manager to delete these 20 remaining names?

But still it's a big positive move, thank you Roy!

tblondet · Mar 15th 2007

Re: Delete Names

Thank you Stan but this macro does not work for my workbook.
If you you to test it, you can access it at:
http://download.yousendit.com/4E1C2D6F46FFD321

Best,

Thomas

tblondet · Mar 14th 2007

Re: Delete All Names

Pretty convenient !

http://download.yousendit.com/4E1C2D6F46FFD321

Thanks Roy.

tblondet · Mar 14th 2007

Re: Delete All Names

Roy,

I am using it on a regular excel spreadsheet (the workbook is quite big though)

It's weird because when I read what people say on the Internet, the macro seems to work for them too.

I can't upload my one sheet TEST zipped file as it says it's too big...

Can I send it to you ?

tblondet · Mar 14th 2007

Re: Delete All Names

Thank you for your answer Roy but I still get the error: "That name is not valid"...

tblondet · Mar 14th 2007

To delete all names, somebody proposes the following solution:

Use Tools > Options > Transition
Make sure that you have the / in the Microsoft Excel menu or help key
Put a dot in Lotus 123 Help
OK

Then use:
/rnr

What does he means by then use /rnr??? I tried to write /rnr in the microsoft excel menu or help key and pressed entered but got the error "separator or menu key must be one character"....

Another person advised to use the following macro:

Code

Sub DeleteAllNames()
Dim name As name
    For Each name In Names
    name.Delete
    Next name
End Sub

I got the error: "That name is not valid"

Help would be greatly appreciated !

tblondet · Mar 14th 2007

Hi,

I looked on the internet for the solution but none of the one presented (macros) are working and I can't have the name manager delete all names at once.

I am trying to find a solution to delete all names in one shot to avoid the problem: "A formula or sheet you want to move or copy contains the name 'a', which already exist on the destination worksheet. Do you want to use this version of the name?" when copying a sheet.

Thank you for your help,

Thomas

tblondet · Feb 28th 2007

Re: Number Of Characters In A Cell

Thank you SHG !

tblondet · Feb 28th 2007

Hi,

Do somebody know how many characters a cell can contain?

Thank you for your help,

Thomas

tblondet · Jan 11th 2007

Re: Cell Selection Formula

If I can participate to that debate , if you want to use the choose function, you have to make sure A1<4, which gives:
=IF(AND(B1<>"",B1<4),CHOOSE(B1,B2,B3,B4),"")

tblondet · Jan 11th 2007

Re: Cells Not Updating - Calculating With Calculations On Automatic

Thank you for your help, that could have been it but I think it was a problem with y data provider (Thomson)..

Anyway, it works now,

Thank you

tblondet · Jan 11th 2007

Re: Cells Not Updating - Calculating With Calculations On Automatic

They are IF statements.

Here is an example:
IF($H$5=$AA$6,K7,IF($H$5=$AA$7,K7,IF($H$5=$AA$8,O7,IF($H$5=$AA$9,R7))))

It is a long formula but it is very simple...:)

Thank you !

Thomas

tblondet · Jan 11th 2007

Hello everybody,

For some reason, my calculations are set on Automatic but some cells B that are linked to cells A do not update when cells A change.

Do you have an idea why ?

Thank you for your help,

Thomas

tblondet · Dec 29th 2006

Re: Can't Display Clip Art On Calculation

Hi Roy,

Here is the code I used:

Code

Private Sub CommandButton1_Click()


Dim TabName1 As String
Dim TabName2 As String
Dim TabName3 As String
Dim TabName4 As String
Dim TabName5 As String
Dim TabName6 As String
Dim TabName7 As String
Dim TabName8 As String
Dim TabName9 As String


'Calculate les nouveaux noms de tab


ActiveWorkbook.Worksheets(Sheet1.Name).Select
Application.Calculation = xlManual
Range("E7").Calculate
Range("AD6:AF16").Calculate


        TabName1 = ActiveSheet.Range("AD6").Value 'Data PUB
        TabName2 = ActiveSheet.Range("AD7").Value 'Data DTAS
        TabName3 = ActiveSheet.Range("AD8").Value 'Data PUB (2)
        TabName4 = ActiveSheet.Range("AD9").Value 'Charts PUB
        TabName5 = ActiveSheet.Range("AD10").Value 'Charts DTAS
        TabName6 = ActiveSheet.Range("AD11").Value 'Charts PUB (2)
        TabName7 = ActiveSheet.Range("AD12").Value 'Output PUB
        TabName8 = ActiveSheet.Range("AD13").Value 'Output DTAS
        TabName9 = ActiveSheet.Range("AD14").Value 'Output PUB (2)


'Renomme toutes les feuilles pour eviter les duplicas
        Worksheets(Sheet1.Name).Select
        ActiveSheet.Name = "-"
        Worksheets(Sheet9.Name).Select
        ActiveSheet.Name = "--"
        Worksheets(Sheet2.Name).Select
        ActiveSheet.Name = "---"
        Worksheets(Sheet10.Name).Select
        ActiveSheet.Name = "----"
        Worksheets(Sheet6.Name).Select
        ActiveSheet.Name = "-----"
        Worksheets(Sheet3.Name).Select
        ActiveSheet.Name = "------"
        Worksheets(Sheet1.Name).Select


'Commence a renommer
        ActiveSheet.Name = TabName1
        
        Worksheets(Sheet2.Name).Select
        
'Data
'Evite que le code ne s'interrompe si les deux tickers sont identiques
            If TabName1 = TabName2 Then
            ActiveSheet.Name = TabName3
            Else
            ActiveSheet.Name = TabName2
            End If
        
        Worksheets(Sheet6.Name).Select
        ActiveSheet.Name = TabName4
        Worksheets(Sheet3.Name).Select
        
'Charts
'Evite que le code ne s'interrompe si les deux tickers sont identiques
            If TabName4 = TabName5 Then
            ActiveSheet.Name = TabName6
            Else
            ActiveSheet.Name = TabName5
            End If
                 
        Worksheets(Sheet9.Name).Select
        ActiveSheet.Name = TabName7
        Worksheets(Sheet10.Name).Select
    
'Output
'Evite que le code ne s'interrompe si les deux tickers sont identiques
            If TabName7 = TabName8 Then
            ActiveSheet.Name = TabName9
            Else
            ActiveSheet.Name = TabName8
            End If
                 
        [COLOR="DarkRed"]Worksheets(Sheet9.Name).Range("AA8:AA13").Calculate
        Worksheets(Sheet10.Name).Range("AA8:AA13").Calculate[/COLOR]
        Worksheets(Sheet1.Name).Select
        Range("L16").Select
        
End Sub

Display More

The part I highlighted in red used to be:

Code

Worksheets(Sheet9.Name).calculate

This worksheet (Sheet9.Name) contains graphs.

Thank you for your help,

Thomas