Add-ins and #NAME errors

I have an Excel spreadsheet that was saved with a series of add-in function 
calls. The add-in wasn't loaded at the time, and the UDF cells contained 
#NAME errors. When I opened the sheet on a machine that did contain the 
relevant add-in, I expected that hitting F9, Shift+F9 or at the very most 
Ctrl+Alt+F9 would recalculate all the cells including the ones with the 
#NAMEs.

However this was not the case. The only way I could fix this sheet was by 
using an F2+Enter combination on the broken #NAME cells. Since the range was 
relatively large, I used a VBA macro with SendKeys.

Though I did manage to fix my problem, I'm a bit dissatisfied with the 
solution. It's not particularly elegant, and if the range is large then the 
SendKeys operation can take some time.

Moreover, I'd like to understand why the #NAME cells didn't self-correct 
when the relevant add-in was present.

Thanks in advance,
Schiz 

0
Schizoid
11/23/2009 11:14:14 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1618 Views

Similar Articles

[PageSpeed] 10

One quick way to F2 + Enter a large range of cells is to do an edit>replace

Select all cells and Edit>Replace

What:  =

With:  =

Replace all will update all formulas.

Your error should have gone away when you loaded the workbook on a machine
with the add-in unless the linking path was different to the relevant
add-in?


Gord Dibben  MS Excel MVP

On Mon, 23 Nov 2009 23:14:14 -0000, "Schizoid Man" <schizoid_man@london.com>
wrote:

>I have an Excel spreadsheet that was saved with a series of add-in function 
>calls. The add-in wasn't loaded at the time, and the UDF cells contained 
>#NAME errors. When I opened the sheet on a machine that did contain the 
>relevant add-in, I expected that hitting F9, Shift+F9 or at the very most 
>Ctrl+Alt+F9 would recalculate all the cells including the ones with the 
>#NAMEs.
>
>However this was not the case. The only way I could fix this sheet was by 
>using an F2+Enter combination on the broken #NAME cells. Since the range was 
>relatively large, I used a VBA macro with SendKeys.
>
>Though I did manage to fix my problem, I'm a bit dissatisfied with the 
>solution. It's not particularly elegant, and if the range is large then the 
>SendKeys operation can take some time.
>
>Moreover, I'd like to understand why the #NAME cells didn't self-correct 
>when the relevant add-in was present.
>
>Thanks in advance,
>Schiz 

0
Gord
11/24/2009 12:38:04 AM

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:f9amg5td2b69g6akf3o7jhdhvo663hgnnv@4ax.com...
> One quick way to F2 + Enter a large range of cells is to do an 
> edit>replace
>
> Select all cells and Edit>Replace
>
> What:  =
>
> With:  =
>
> Replace all will update all formulas.
>
> Your error should have gone away when you loaded the workbook on a machine
> with the add-in unless the linking path was different to the relevant
> add-in?

Hello Gord,

Thanks for the reply. No, I've seen this problem occur with two different 
Excel add-ins. The #NAME error persists unless a brute force F2+Enter is 
performed on the erroneous cells. How do I determine the linking path 
configuration?

Thank you,
Schiz 

0
Schizoid
11/24/2009 9:12:49 AM

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:f9amg5td2b69g6akf3o7jhdhvo663hgnnv@4ax.com...
> One quick way to F2 + Enter a large range of cells is to do an 
> edit>replace
>
> Select all cells and Edit>Replace
>
> What:  =
>
> With:  =
>
> Replace all will update all formulas.
>
> Your error should have gone away when you loaded the workbook on a machine
> with the add-in unless the linking path was different to the relevant
> add-in?
>
>
> Gord Dibben  MS Excel MVP

Hi Gord,

I found the source of the error: http://support.microsoft.com/kb/291058. The 
solution is do a Find-Replace action on the formulas, which is easy enough 
to do. Definitely a lot more elegant that iterating through the entire 
range.

Schiz 

0
Schizoid
11/24/2009 11:33:51 AM
Wasn't that what Gord suggested?

-- 


Regards,


Peo Sjoblom


"Schizoid Man" <schizoid_man@invalid.com> wrote in message 
news:82614503-35D5-4937-AC56-8AF358C8CFD6@microsoft.com...
>
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message 
> news:f9amg5td2b69g6akf3o7jhdhvo663hgnnv@4ax.com...
>> One quick way to F2 + Enter a large range of cells is to do an 
>> edit>replace
>>
>> Select all cells and Edit>Replace
>>
>> What:  =
>>
>> With:  =
>>
>> Replace all will update all formulas.
>>
>> Your error should have gone away when you loaded the workbook on a 
>> machine
>> with the add-in unless the linking path was different to the relevant
>> add-in?
>>
>>
>> Gord Dibben  MS Excel MVP
>
> Hi Gord,
>
> I found the source of the error: http://support.microsoft.com/kb/291058. 
> The solution is do a Find-Replace action on the formulas, which is easy 
> enough to do. Definitely a lot more elegant that iterating through the 
> entire range.
>
> Schiz 


0
Peo
11/24/2009 6:14:40 PM
"Peo Sjoblom" <Someone@mvps.org> wrote in message

> Wasn't that what Gord suggested?

Hello Peo,

Yes - you're right, the ultimate solution was to do a replace in the code 
using VBA, so exactly what Gord suggested. What had (and frankly, has) me 
puzzled is that Gord said that if I recalculate the sheet on a machine that 
has the required add-in the calculations should work out of the box.

However my experience is that this is not the case. Without a replace of the 
'=' sign this error will not go away. Any ideas why this occurs?

Regards,
Anuj 

0
Schizoid
11/25/2009 10:01:31 PM
Reply:

Similar Artilces:

http_403 error
This has been cross posted in the windows.sbs news group. Ok, I am going nuts here, can someone help me? I'm using Microsoft SBS 2003. I get a 403 error from any device trying to sync. I have checked all the VD's and they seem fine. Here is the IIS log: 2005-08-23 02:57:07 203.79.110.126 OPTIONS /Microsoft-Server-ActiveSync User=neill&DeviceId=B77507360000000108000050BF3F5173&DeviceType=PocketPC 80 - 166.179.17.182 Microsoft-PocketPC/3.0 401 2 2148074254 2005-08-23 02:57:09 203.79.110.126 OPTIONS /Microsoft-Server-ActiveSync User=neill&DeviceId=B77507360000000108000050B...

Replace a comma with a period in a cell containing a lastname, first name, middle i
Hello - I am trying to clean some data and need to change all of my names from McLaughlin, Victor, (i.e, comma) W to McLaughlin, Victor.(i.e., period) W Is there an extract and replace formula or method of som sort (in excel or access) that will allow me to pull the first comma from the right and replace it with a period. Thanks for any suggestions! Select the cells you want to change and run this tiny macro: Sub comma_tose() For Each r In Selection v = StrReverse(r.Value) r.Value = StrReverse(Replace(v, ",", ".", 1, 1)) Next End Sub For example: a,b,c,d wi...

SMTP domain name
Hello. I am running Exchange 5.5 SP4 on a windows 2000 machine and a windows 2000 native domain. Problem I am having is complaints from people regarding the smtp domain name. Our Public domain name and our AD domain name are different. Helo response from the server returns the AD domain name and not the public domain name. This is causing messages to be rejected by some SPAM filters. Has anybody had this problem? Thanks Yes I have battled long and hard with this problem. First thing to check is Exchange System Manager, "Default SMTP Virtual Server" look in advanced propertie...

Encryption error
When I open Money 2004 I get the following error "Unable to encrypt data at the required level" Tells me to go to a site and install some security updates, which I did. I have all the latest patches and updates (except XPSP2, it sucks). Have latest IE 6 with SP1 and latest encryption pack. Help! In microsoft.public.money, "Encryption error..." <Encryption error...@discussions.microsoft.com> wrote: >When I open Money 2004 I get the following error > >"Unable to encrypt data at the required level" > >Tells me to go to a site and install so...

Circular Reference Error
Can anyone see what would be causing me to get a circular reference error??? Thanks! SELECT [BartS1Report].Customer_ID, [BartS1Report].Customer, [BartS1Report].Service_Address, [BartS1Report].Employee, [BartS1Report].Manager, [BartS1Report].Type_of_System, [BartS1Report].Raw_Water, [BartS1Report].Treated_Water, [BartS1Report].Cycles, [BartS1Report].Inhibitor_Level, [BartS1Report].Range_1, [BartS1Report].Range_2, [BartS1Report].Range_3, [BartS1Report].Range_4, [BartS1Report].Range_5, ((SELECT Sum([XX].[ZZZ]) FROM [BartS1Report] AS [XX] WHERE [BartS1Report].Customer_ID & ...

Add Word and change format
1) Let say colomn A is a product codes, such as "PK0021", "UQ05P8", etc...Now I want add a "Z" in front the codes. To be "ZPK0021, ZUQ05P8". What's the faster way in case I got thousand of codes? 2) In my colomn B is such code as "18-521-65, 18-81-84, 18-1112-65" and etc. Now I would like to make it to be standard to 4 digit for the middle number to be "18-0521-65, 18-0081-84, 18-1112-65" ... As the same senario as above, I got more than thousand of such codes... What's the faster way? Kelvin The first could be done wi...

Autofill Error
Hi, I need a macro to autofill COLUMN A with a word say YES until the last row found in COLUMN B. Right now I have the ff codes: Sheets("VOUCHER - STEP 2").Select Range("A5").Select ActiveCell.FormulaR1C1 = "DEBIT" With ActiveCell.Characters(Start:=1, Length:=5).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineSt...

Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug because there are also a lot of rows. Is there anyway to change display so it replaces the column name e.g. If(BT1204="X". BA="Y" to If(CustomerName="X", CustomerCode="Y") ? CustomerName is a defined name range for BT1204 Find & Replace Find what: BT1204 Replace with: CustomerName "msnyc07" wrote: > I have a # of fairly long/complex cell functions that get hard to debug > because there are also a lot of rows. > > Is t...

Display names in Contacts
When I create a new contact and after that I type the email address, then Display name is written like this : LastName FirstName(email). Is there a setting that I can change to eliminate the email address in the display name ? I would like to have the display name showing like this : LastName FirstName . Antoine Veilleux Change the 'Display as' setting when creating new contacts. "Shidewa" wrote: > When I create a new contact and after that I type the email address, > then Display name is written like this : LastName FirstName(email). Is > there a settin...

address being appended to contact's name
If this is not the proper newsgroup for windows mail, please direct me to it. When I create or forward email, the email address is appended to the name of the contacts in the "To:" line. How can I stop this from happening? Thanks, Jim B. Vista's Windows Mail? microsoft.public.windows.vista.mail "Jim Birke@yahoo.com>" <jimbir<nottthis> wrote in message news:#RKwAWMuKHA.4796@TK2MSFTNGP02.phx.gbl... > If this is not the proper newsgroup for windows mail, please direct me to > it. > > When I create or forward email, the email...

Can I use VBA to add cells (over blanks) then do multiplication
I have a Word table in which the last column contains numbers (3 and 4) and some bank cells and I want it add them and put the total into the second last row (7 in this case). The last row contains a multiplier (3) which when applied to the total results in 21. Below is the table. | | | 3 | | | | | | | | 4 | | | | 7 | | | 3 |21| How can I achieve this in VBA (under Word 2003 and 2007) remembering that the user can add rows to the table and the last column can contain blank cells. Thanks in advance for any assistance, Peter Evans Sub ScratchMaco(...

How to add a host in safe sender list
Dear ALL, How can i config IMFv2 to unblock all emails sent from a specific host in my company? eg. all emails sent from 10.0.0.1 should not be blocked. Thanks a lot - Add it to the Connection Filtering - Global Accept list: Global Settings | Message Delivery properties | Connection Filtering | Accept. - Enable Connection Filtering in SMTP virtual server properties | General tab | Advanced | select IP(s) | Edit | Apply Connection Filter. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "...

Mail error
I have been using outlook for a week and it was working fine all of a sudden i cannot retreive any emails, i can send but not receieve in outlook. i have a Hotmail plus account the service status in oultook says Mail: error Calender: connected Contact: connected can you help me with that?? ...

2003 code raises error in 2007
I have a reference in VBA to a subform of a subform, which works fine in Access 2003. In Access 2007, however, it results in "you have entered an invalid reference to the property Form/Report." This is the snippet of code: Private Sub SetFormProperties() 'called from form_open, makes all forms editable if edit mode was selected Dim ctrl As Control, ctrlSub As Control Dim frm As Form If Me.OpenArgs = "Edit" Then Set frm = Me 'set main form edit options EditProperties frm 'set subform edit options For Each ctrl In frm If ct...

VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include a number with 5 digits then a city name then a region name, such as "11090 CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this field changed as following : "F-11090", then copying "CARCASSONNE" into the City field which is empty (column G). The city name is always starting just one space character after the postcode, same thing for the region name, it always starts one space character after the city name. The region has to be removed completely. ...

Outlook errors #4
Since upgrading to outlook 2003 I cannot view any items in the sent folder. I have tried uninstalling and reinstalling but get the same error- "outlook has encounted an error and will close" I can view the sent items via OWA, so I know they exist. I have also tried setting up the account on a different pc. Any help would be very helpful. Have you tried a new mail profile? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without ...

Outlook 2003-Failing remember name when typing into the To: field
I just got Outlook 2002 and I was informed that when you send a message and typing in the To: field that if you have emailed someone before it would remember the name. For example: If I send an email to Shawn Hedrick the next time I send one I would only have to type maybe the first few letter and then it would prefill the rest. Does anyone know about this or how to set this up? tools, options, email options, advanced options - is suggest names checked? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStar...

"Get-ClusterResource" cmdlet throws "not recognized as a cmdlet, function, operable program, or script file" error.
Hi, I am trying to use "Get-ClusterResource" cmdlet to fetch "File Share Witness" property of CCR cluster. But when I run this command no "Exchange Management Shell" I am getting "The term 'Get- ClusterResource' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again." error. Any idea why I am getting this error? Do I need to add any "PSSnapin" via Add-PSSnapin cmdlet ? If so which one it is? Basically I am not able to run any of the cluster related GET cmdlets on Exchange Managem...

E2k3: An ambiguous SMTP proxy SystemMailbox error 9549
I get this message a few times a day and the best I can figure is I have two mailboxes found in the Exchange System Manager with the same string of characters in their names. SMTP (SERVER1-{A643FE61-xxxx-4B74-B902-280BFB58699B}) SystemMailbox{A643FE61-xxxx-4B74-B902-280BFB58699B} However this condition is identical on two exchange servers (slightly different numbers) but only one exchange server is getting the error. Any ideas? Event Type: Error Event Source: MSExchangeIS Event Category: General Event ID: 9549 Date: 1/9/2005 Time: 2:28:44 PM User: N/A Computer: SERVFL01 Description: An...

Error sending email and editing email's body
I have the following function Private Sub SendEmail_Click() Dim strSubject As String Dim strEmailList As String Dim strMessage As String Dim strMessage2 As String On Error GoTo Err_SendEmail_Click strSubject = "Please investigate" & " " & [Ticket #] strEmailList = "JohnDoe@emailaddess.com " strMessage = Me.Text_Description strMessage2 = "Assign to:" DoCmd.SendObject acSendNoObject, , acFormatRTF, strEmailList, , , strSubject, strMessage, strMessage2 Exit_SendEmail_Click: Exit Sub Err_SendEmail_Click: ...

On pre-printed paper(picture) how do I add script in white
I am trying to print white script on a pre-printed program. When sent to the printer the letters do not show. I have added fill to text box, but still letters do not print to sheets. I need help! If your printer does not have white ink, you can't. From what I've heard, printers that do have white ink are very expensive. -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "bearbiz" <bearbiz@discussions.microsoft.com> wrote in message news:8ECBB048-CD15-4DC8-A425-48287DEB7EB9@microsoft.com... >I am trying t...

Error when drawing grid, grid lines a bit too short
Hello, as I've mentioned in some other post I'm making a simple "Game of Life" with a small, fixed-sized grid. Anyway, the function I used for drawing the actual grid was written ages ago when doing some pure Win32 programming and it was full of magic numbers, assuming a 16*16 grid. I tried to rewrite it without magic numbers and to take the actual size into consideration. Right now each cell in the grid is 20*20 and I have horizontal and vertical bars that are four pixels wide. The x and y offset is 20 pixels. Hope I explained it properly, here's the code: voi...

add pf To Other Calenders
This is a multi-part message in MIME format. ------=_NextPart_000_00B9_01C9724A.11E97A30 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 8bit Hello, I try to add a public calendar ( in my case a Public Folder on my exchange server ) to Other Calendars in outlook: All my clients have outlook 2007 installed. On a few pc's, after i click on 'Add To Favoritesi' the Calendar is not added and i cannot see the calendar in 'Other Calendars' Any idea, how to solve this? Tx, I follow these instructi...

Unknown programming error. Error={0x80020009]
When replying to or forwarding emails I keep receiving this error message: Unknown programming error. Error={0x80020009] I am using Outlook 2000, Windows XP. ...

Using Access to create an appointment and add attendees
Hi i am using Access to create an appointment in outlook. I have the code for creating the appointment but i am struggling to add attendees. This is what i have so far; DoCmd.RunCommand acCmdSaveRecord Dim objOutlook As Outlook.Application Dim objAppt As Outlook.AppointmentItem Dim objRecurPattern As Outlook.RecurrencePattern Set objOutlook = CreateObject("Outlook.Application") Set objAppt = objOutlook.CreateItem(olAppointmentItem) With objAppt .Start = Est_Finish_Date .Subject = Component ...