Text quote marks in Macro

If have a macro which is to replace a formula in a cell where the operator 
has the option of either accepting the formula result as a default or 
overtyping with a different value. A "Reset" button runs the macro that 
restores the formula into the cell.
The line that does this is ..
Range("New_Sec") = "=If(Purpose_1="Upstamp",0,1)" 

The obvious problem is that the Macro needs to see the formula as a string 
so that it can be put back into the cell, so it is enclosed in quotes, and 
the formula needs to have "Upstamp" defined as a string, so it also is 
enclosed in quotes and the two sets of quotes causes an error.

What is the solution for this?
0
KenG (52)
11/15/2004 2:17:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
336 Views

Similar Articles

[PageSpeed] 22

Double them, just as in XL:

    Range("New_Sec") = "=If(Purpose_1=""Upstamp"",0,1)" 

In article <DA35E9C4-230D-4ECD-982E-237B86A642F8@microsoft.com>,
 Ken G. <KenG@discussions.microsoft.com> wrote:

> If have a macro which is to replace a formula in a cell where the operator 
> has the option of either accepting the formula result as a default or 
> overtyping with a different value. A "Reset" button runs the macro that 
> restores the formula into the cell.
> The line that does this is ..
> Range("New_Sec") = "=If(Purpose_1="Upstamp",0,1)" 
> 
> The obvious problem is that the Macro needs to see the formula as a string 
> so that it can be put back into the cell, so it is enclosed in quotes, and 
> the formula needs to have "Upstamp" defined as a string, so it also is 
> enclosed in quotes and the two sets of quotes causes an error.
> 
> What is the solution for this?
0
jemcgimpsey (6723)
11/15/2004 2:33:12 AM
Reply:

Similar Artilces:

Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...

How do I fix this so I don't get an "Error 2015"
Dim x x = Application.CountIf("E5,E6000", "T13065") How do I fix this so I don't get an "Error 2015" Thanks, Rick Not tried it but I would think x=Application.Countif(Range("E5:E6000"),"T13065") Providing T13065 is a string you are counting. If it's a range reference x=Application.Countif(Range("E5:E6000"),Range("T13065")) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "2D Rick" <rbrowniii@compuserve.com> wrote in m...

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

How to get a macro to do what i want?
I have designed a form in Excel and the data that is inputted i want to be able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. Calculate the last row ...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...

Macro to apply alternate grey/white shading to selected paragraphs
Dear Experts: I would like to do paragraph shading using grey shading alternately. Example: this is a sample text This is another sample text This is yet another sample text This may be the last paragraph acquiring grey shading This is the penultimate paragraph This is the last paragraph With these 6 paragraphs selected, I would like to run a macro which applies grey shading alternately to the selected paragraphs, i.e. grey/ white (no shading) alternately. Help is much appreciated. Thank you very much in advance. Regards, Andreas Use Dim oRng As Range For i = 2 T...

Insert Static Date as part of Macro
I've built a nice macro, but I want it to insert the date it is run in an empty cell within the macro's range. When working within a spreadsheet, Ctrl +; returns the system date, but I can't figure out how to put that command into a macro. The macro recorder editor shows that the system recognizes the entry as the current date, not a function that returns the current date. The now() or today() functions are both dynamic ... they change with the system date. That's not acceptable in this situation. I need to seen when the macro was run. Thanks for any help, Ralph di...

Macro Security, Password Recovery & VB Code execution
I have found that if I set my macro security to high, VB code within (non-trusted) excel sheets do not execute... a microsoft security feature that ostensibly prevents non- trusted users from running VB The questions I have are : Is there a way to guarantee VB macro-execution using digital certificates or what-have-you ? Is there a way to detect, either within excel (well, not possible, it seems) or from outside, that the combo of users macro-security settings and signature status of a perticular excel file, WILL prevent VB from executing, if the file is attempted to opened ? ...

Can't insert rows
I am using Excel 2007 and have just loaded a spreadsheet created in a previous version which is running in Compatibility mode. When I try to insert a row I get an message "Cannot shift objects off sheet". How do I insert rows on this sheet? No problem with another workbook loaded at the same time which was created in 2007. See if this helps http://support.microsoft.com/default.aspx?kbid=211769 "Cannot shift objects off sheet" error message when you hide columns in Excel -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my...

"me." doesn't work
I write a lot of VBA. I find it very useful to start lines referring to the object I'm writing with "me." so that I can get the list of properties and methods. In the project I'm currently working on, this has suddenly stopped working. I type "me." and nothing happens. I can't figure out if I've accidentally disabled a feature or something like that. Anyone have any ideas? Thanks much in advance. "accesswanabe" <accesswanabe@discussions.microsoft.com> wrote in message news:B6B82001-53C9-4932-9BA5-D9D6B454DE1B@microsoft.c...

Macros and Active X
My ctrl+c and ctrl+v shortcuts dont work unless I turn on my active forms bar to edit mode. Any guesses why -- Message posted from http://www.ExcelForum.com Because the spreadhseet is "live". You have to go into edit mode t make any changes to control toolbox items. -- Message posted from http://www.ExcelForum.com I'm not trying to edit my active x forms, The problems is that th standard ctrl+c and ctrl+v copy and paste short cuts wont work unles the active x edit mode is turned on. I don't have to turn on the active x edit mode to use these short cut in other wo...

Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that pa...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

Find Replace with Macro
Can anyone tell me how to construct a macro that will seacrh the entire contents of column C, find the contacts of cell A1 and replace it with B1, then do the same finding A2 and replacing it with B2 in column C, then A3 with B3 in column C etc etc. I can not get this to work. Sub Test() Dim r1 as Range Dim r2 as Range For Each r1 in Range("A1:A" & Range("A65536").End(xlUp).Row For Each r2 in Range("C1:C" & Range("C65536").End(xlUp).Row If r1.Value = r2.Value Then r2.Value = Range("B" & r1.Row).Value Next Next End Sub -...

Help replacing text with Yes or No
I have a field formated as general. The field contains either 1 or is left blank. If the field has a 1 I want to replace it with Yes and if the field is blank I want to replace it with No. any help is appreciated. -- Jerry Save your data and use a copy for this exercize........... Assuming your data in Column A, put this in B1 and copy down........ =IF(A1=1,"Yes","No") Then highlight the column and do Copy > PasteSpecial > Values to get rid of the formulas..........then delete column A if you wish....... Vaya con Dios, Chuck, CABGx3 "Jerry Arnone, ...

Task requests lacking "Accept" button
In Outlook 2003 (SP2, all updates) on my notebook, the task requests that I receive are lacking the clickable button to accept or reject them. If I poll the same task requests on my desktop machine, they do show the missing buttons. I tried scanpst.exe and a new profile, but in vain. Similar behavior of task requests was reported in this group repeatedly, recently in http://groups.google.de/group/microsoft.public.outlook/browse_thread/thread/5eafcfdd852be150/955da670ca93e396?hl=de&lnk=st&q=#955da670ca93e396 What can I do? Salut, Aristide On 7 Nov., 00:31, Aristide de la Tanne <a...

Outlook 2007 "profile"
Frustrating. When I open Outlook 2007, the program displays a "Choose Profile" screen (with only one option, called Outlook). This is rather new, and I don't know what I did to cause this. I simply want Outlook to open normally without this Profile screen. Any thoughts or comments? By the way, I did check the block that says to make this profile the default -- that didn't help. Thanks. EW Open the mail applet in the control panel. Select show profiles button. Your after the settings at the bottom of the dialog. "Lone Star" <ewyatt_del_me-@excit...

Water Mark Line
anyone know how to insert a "water line", meaning a specific value that overlays the graph Hi not quite sure but maybe the following is what you're looking for? http://andypope.info/charts/averageline.htm http://www.tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine -- Regards Frank Kabel Frankfurt, Germany David wrote: > anyone know how to insert a "water line", meaning a > specific value that overlays the graph ...

Error, "The name could not be resolved. The action could not be completed".
Hi gurus, I have an Outlook 2002 client which talks to his mailbox on an Exchange 2000 server. Whenever he tries to go through the steps to add a second mailbox (in addition to his own, when opening Outlook), he receives the error: "The name could not be resolved. The action could not be completed". This error occurs as soon as he hits the "Add" button, before he even gets a chance to add in the mailbox name. For Outlook, this is an Exchange Server properties advanced setting. Very odd. Spin wrote: > Hi gurus, > > I have an Outlook 2002 client which talk...

'Text' instead of number
I have spent two days manipulating data (about 300,000 cells' worth) I imported as Fixed-Width (from MAS-90, if anyone cares). The final step involves multiplying two numbers. The POSITIVE numbers imported fine, the NEGATIVE numbers imported as X.000- and give me a #VALUE! error when I use any math function with them. I have tried the Trimall macro. I am guessing that somehow Excel thinks the '-' is a DASH and not a negative... How do I change that???? See: http://www.mcgimpsey.com/excel/postfixnegatives.html HTH Jason Atlanta, GA >-----Original Message----- &g...

Clearing "Find" History in OL 2002
I have used the "find" option to locate emails and now wish to clear them out and start fresh. There have gotten to be too many in my drop down list. How do I do this? ...

Excel "data base engine failure"?
I am merging data from an Excel chart into a Word document to create a phone directory for our church. I've done merges many, many times before successfully. However, I am not able to do so at this time and get the following message: "Data base engine failure". Help. What do I do? On Sun, 21 Mar 2010 14:57:01 -0700, Phone Directory Delima <Phone Directory Delima@discussions.microsoft.com> wrote: >I am merging data from an Excel chart into a Word document to create a phone >directory for our church. I've done merges many, many times before &g...

"Do you want to save changes" not working...
I copied an excel chart into powerpoint as "Excel Chart (entire workbook)" so that it would not have any links to the orginal worksheet. When I then go in to edit the data the changes are made to the chart however if I close the powerpoint presentation it does not ask me if I want to save my changes, and when I reopen the presentation the chart goes back to the orginal data. If I consciously save the presentation before closing it, the changes made, however I'm afraid I might close it without remembering to save it since I'm not prompted. Is this a problem wi...