including blanks or text values in macro-entered formulas

When I try to use the following codes, I get error messages. How do 
get around the problem?

ActiveCell.Formula = "=if(A1=1,"",1)"


ActiveCell.Formula = "=if(A1=1,"High","Low")"

Thanks so much for any assistance


Message posted from

6/25/2004 5:13:14 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 31

Just as in XL, quotation marks within a string need to be doubled (or 
use CHR(34)):

    ActiveCell.Formula = "=IF(A1=1,"""",1)"


    ActiveCell.Formula = "=IF(A1=1,""High"",""Low"")"

In article <>,
 Matthew McManus <<>> 

> When I try to use the following codes, I get error messages. How do I
> get around the problem?
> Cells(2,2).Select
> ActiveCell.Formula = "=if(A1=1,"",1)"
> or
> Cells(2,2).Select
> ActiveCell.Formula = "=if(A1=1,"High","Low")"
> Thanks so much for any assistance
> Matthew
> ---
> Message posted from
jemcgimpsey (6723)
6/25/2004 5:23:27 AM
Thanks for that - I thought that there had to be a simple solution

Message posted from

6/25/2004 5:41:21 AM

Similar Artilces:

Fun with Time values
Hello all, The boss gave me a new one today, and there is one formula I can no get my head around. Here is the situation: In part of the Excel sheet, I have a colum listing scheduled deliver times for various locations. These Time Values are static, i.e non-changing. Each colum to the right of these projected Time Value will contain Actual Delivery times. One colum per each day of the mont for Actual Delivery times. What I need to do with these values is this: 1) Automatically highlight any Delivery Time that exceeds the projecte Delivery Time by 1 hour. This I have done with Conditional...

Creating an autosend email macro
Whenever I log in to my machine when I first get to work I would like to have an email sent to my boss that lets him know that I am in the building. I tried the macros but I am only used to doing macros with step-by-step recording not with the VB editor. How do I go about doing this? ------------------------------------------------ ~~ Message posted from ~~ View and post usenet messages directly from See if the information on the following page helps: -- Jocelyn Fiorello MVP - Out...

Formatting New Text Styles
Hi, I have been experimenting with creating new text styles in Publisher 2000. I have noticed in the "Indents and Lists" section that when I choose an Option e.g. Hanging Indent, the Pre-sets box displays "Custom", is this always the case. Thanks in advance Nick Bradbury ...

range select a value in a cell?
I have formulas calculating values to produce cell references such as J10, L15 etc the formulas are similar to ="I"&TEXT(Q4, "d") I am creating a macro where I want to select the cells calculated from my formulas... instead of using range("J10:K10").select i want the range to use the specific cell reference calculated in my formula... because the range i want to select changes every time a new record is added. is there a way to do this? any help is much appreciated. thanks b2: a2:a4 c2: =SUM(INDIRECT(B2)) --- HTH, David McRitchie, Microsoft MVP -...

Scanned text exported to Word
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Using an Epson 4990 scanner and Acrobat 8 Pro's OCR, scanned text is exported to a Word doc wonderfully. But Frames enclose the text, many or few. Right-clicking on the Frame brings up a contextual menu that includes Format Frame. Clicking on this brings up the Frame formatting box which includes Remove Frame which works. <br> Or I can copy and paste the contents of the Frames to another Word document. <br> This can be very tedious and time consuming as there may be many Framed enclosed text. &...

Compare/Sepearte Values from Cell
I have a set of survey data in Excel format. Some questions are in the format of "check all that apply" and the answers i have are in a single column of cells with each answer seperated by a coma. The answer could also be "other". For some reason, the survey doesn't mark the answer as other, but simply record the data, so i can have answers like A,B,C C,E,custom input data1 A,custom input data 1 I want to get the data into seperate columns with binary indicating values like A | B | C | D | E | Other | Value of Other 1 1 1 1 ...

Blank Subject Line Rule
Hi, Can somebody please tell me if I can setup a rule in Microsoft Outlook to move all messages with nothing in their subject line to a specific foloder? Thanks in advance. Rabih I third that. "Bill T. Ray" <> wrote in message news:%23S9QU5V8DHA.2796@TK2MSFTNGP09.phx.gbl... > I think many of us would be interested in this also. > > Bill T. Ray > > "Rabih Najib" <> wrote in message > news:uKxvRPa6DHA.2644@TK2MSFTNGP11.phx.gbl... > > Hi, > > > > Can somebody please tell me if I...

Macro: ajust the print of a range to 1 page wide/tall
Hey guys It annoys me that I constantly have to ajust my prints to 1 page tall/ wide by using the printing tools, and put my self together in intention to program a little piece of macro that will give me joy and laughter back... However - my skill are not as big as my selfconfidence, and failed - so I have to send this discrete inquiry to the people of Excel-jungle - full of vulnerable expectation of getting some help here. My prosedure: The user marks his range. The macro prints this as ajusted 1 page print. My effort: ActiveSheet.PageSetup.PrintArea = "B1:N53" With Activ...

Formulas #3
Hola Buenos Dias. Mi caso es el siguiente. Tengo una hoja con varias formulas que retonan valores numericos. Cada cinco minutos presiono la tecla F9 para actualizar el valor, ya que las formulas trabajan con la fecha y hora del PC. Esta es la segunda vez que me ocurre, al cerrar excel y volver a abrir, aparecen escritas las formulas en las celdas, pero realiza ningun calculo. Si me pueden ayudar se los agradeceria..!!! Cordialmente, Serafo "Serafin Salazar" <> wrote in news:e$b8gXx7DHA.1948@TK2MSFTNGP12.phx.gbl: English translation follows. > Hola Buenos D...

macro to save file automaticaly?
I already have a button with a macro to hide the empty rows. With this same button I want to save the file automatically after the rows are hidden. The name of the saved file should be the word in cell (“sheet3” “A1”). The first macro works the second doesn’t. Can someone please help me to fix the second and to combine the to. Thanks!!! Private Sub CommandButton1_Click() Dim X As Integer For X = 5 To 150 If Cells(X, 1).Value = 0 Then Rows(X).Hidden = True End If Next X End Sub Sub SaveArchive() ArchivePath = "Z:\david\werkbonnen\" WorkBookName = Worksheets(“print”)....

Comparing text and returning boolean values
Hi, I need to compare text values in one cell to text values in another range of cells. Basically I need a formula that tells me if the text in cell E406 equals the text in any of the cells between M3 and M403. I tried =E406=M3:M403, but that didn't work. If anyone knows how to do this please let me know! -- nicoleeee ------------------------------------------------------------------------ nicoleeee's Profile: View this thread: How about this: =ISNUMB...

hyperlink to value #2
frank, thanks for the reply. here is the situation: i use a macro to create hyperlinks. the hyperlinks show up a Sheet2!A1. i then use a line in the same macro to truncate the !A1, an the end result (visible) is Sheet2 (still a hyperlink). i would like t be able to use that value in a formula. is that possible? now, thi part of it needs to be done using a worksheet function only, as th formulae might have to be changed depending on the requirement. peopl not cognizant with macros might be wary of doing that. hence the nee for a worksheet function to convert this hyperlink value to tex val...

Text function
Folks Is it possible to do a custom number format into the text function which will allow me to colour the text similar to conditional formatting. The problem i have is in one of the reports I have the staff have used one cell to try and put 2 entries in with a slash in between. Say this is a target value / agreed value and then at the end of the month I will get an actual value in another cell which I will want to compare against each and give a percentage increase or decrease. I have managed to write a formula to seperate the values and give a percentage back using the text function but now...

combo box
I have a combo box (not activex) that pulls it's values from a column that varies in length. Currently I'm using the entire row (A:A) for the input range. As you can guess, this results in MAAANY blank entries in the box. Is there a formula or some other way so that the combo box will stop at the end of my range of data without writing a macro to load it that way? If you're using code to assign the entire column (not row) to the dropdown (is it a dropdown from the Forms toolbar), you could modify it to use just the used cells. If you're assigning it manually and you don'...

Sorting a column by using formula #2
my sheet goes like this Cell A1: Mr. abc Cell A2: Mr. XYZ Cell A3: Blank Cell Cell A4: Blank Cell Cell A5: Ms. Lee Cell A6: Mr. PQR The data in these cells is coming from another sheet. Now i Actuall want to delete the blank cells in between this data i.e A3 & A4, an get the outcome in another sheet. Can you pls help on this. -- Prais ----------------------------------------------------------------------- Praise's Profile: View this thread: Praise, As you ...

Formula to Count and Return Most common Value in a Dynamic Named Range #2
Hi Frank, Thanks for input. I've used this Formula referencing the cells wit the A1-Style and works ok. Can you explain the use of =1 in this Formula: =MODE(IF(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$9)-ROW($A$2),0))=1,B2:B9)) I'VE TRIED TO AMEND THE FORMULA SLIGHTLY TO USE THE DYNAMIC NAMED RANG WITHIN THE FORMULA - CAN YOU HELP =MODE(IF(SUBTOTAL(3,OFFSET(INDEX(NAMEID,1,1),ROW(NAMEID)-MIN(ROW(NAMEID),0),1),RESULTS),,))??? WHEN I USE THIS REPLACED WITH MY A1-STYLE REFERENCES - I GET ZER For counting the number of values lets assume you have entered the above formula in C1. Then use:...

I would like a macro that would print all excel files (some with multiple tabs) in a folder, and it's subfolders, based on a few criteria. Criteria: 1. There will be files in the folders that don't need to be printed, but all files follow a specific naming convention that would allow me to specify which files should be printed. For example, for Ocotber I would want to print all files whoes name ends in OCT06. Maybe a message box with a spot to specify what I'm looking for in the file names? 2. I don't want to print any files in any subfolders called VOID. This print job coul...

Excel formula #6
I am using the formula - =COUNTIF(E913:E914,"COR") to count the number of times the word COR occurs in my document. I got the formula from Excel Help in the program. The problem is that it won't count, the answer is always zero even though COR appears several times in Column E. Am I using the wrong formula or even entering it wrong? Help! -- Bonni The formula you posted: =COUNTIF(E913:E914,"COR") counts the number of cells that contain only "COR" (not case-sensitive) in the 2 cells you referenced (E913 and E914). Is that your situation? Or may th...

Update Query: Enter Parameter Value
I'm trying to set a field to a value that is stored in a variable using an update query in VBA (Access 2003). The query prompts the user to enter parameter value, and the value of the variable displays in the pop up above the input text box of the pop up. The SQL is: DoCmd.RunSQL "UPDATE temptbl_ImportedPlateOrders SET temptbl_ImportedPlateOrders.GenSetName =" & strGenSetName strGenSetName is the varialbe and displays the correct value in when I mouse over it: strGenSetName = "G9999V00.txt" Can someone identify what I'm doing wrong. Thanks...

Aligning Text To Pleading Paper Template Numbers
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I would please like some guidance on how to accurately line up the rows or lines of text so that they run precise to the line numbers that are in a separate column outside of the left border of the legal pleading paper template that I downloaded from the first link text choice that I found through Microsoft Office for Mac 2008 Word Help. I've tried adjusting the Top and Bottom Margins, as well the Footer and Header Margins, but the numbers of the template adjust just the same with the text in each attempt. So what I...

change text color when the value become lower than a certain value
Is it possible to set the text color which can be changed automatically. Say, if 50 is the middle number, the number in the cell higher than 50, the text color become no change, otherwise, the text color will be changed for my preference. 1. Select the cell. say A1 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Cell Value Is' and select 'Less than' and enter the value directly or refeerence another cell with that value (50) 4. Click Format Button>Font and select your color (say Red) 5. Hit OK PS: If you are using XL2007 Goto H...

How to have the Y-axis values on both sides
I am working with a chart that where I would like the Y- axis to be on both "ends" of the chart (right and left). If that is not possible I would like the y-axis values to be on the right side, instead of the usual left side. Anyone knows how I can do this? Hi Peter, To get the y-axis on the right select the x-axis and on the scale tab of the format dialog check the 'Value (Y) axis crosses at maximum category' option. To get two sets of y-axes you will need to add a dummy data series and move it to the secondary axis. If you use the original data in the dummy s...

Entering Scheduled Debit Transactions as 'Unreconciled'
Am I missing something, or is it not possible to set up a scheduled bill in MS Money 2002 for 'direct debit' and have it go into the account as 'unreconciled'? I've started specifying 'other' rather than 'direct debit', but it seems bizarre to me that the transactions are automatically flagged as reconciled. To me, reconciled means 'Yeah, I verified the bank statement, and the bank and I agree.' Cleared would make sense, but not reconciled. Any help appreciated. -- Ron Ruble M02 has a bug thus: If you r-click on a reconciled (or cleared) tra...

"Print This Page" Macro
I had a spreadsheet template with columns as follows (and rows of about 110 items): Quantity Item Cost per Unit Hours per Unit Ext. Cost Ext. Hours Ground Mat/Grid 2.00 0.00 Ground Rod/Point 0.30 0.00 The Ext. Cost and Ext. Hours columns have formulas that will multiply the quantity by either the cost per unit or hours per unit (or both). There are also rows at the end of the list of items that do NOT contain item names, cost per unit amounts or hours per unit amounts. They only contain the extended cost and extend hours formulas. ...

Want chart to be blank (gap) where a formula returns blank
Hi guys How do I get a chart to return a blank where I have written a formula returning blank based on a value. The problem is I have all my options set to show only plot visible cells and where a cells is empty leave blank. The problem is that I have a formula that says if a value is zero make it "" ( blank). My problem is where I have the formula is in my data that I am adding into my series so when it charts the data it shows every cell that is visibly blank because of the formula as actually a zero value. If I delete out the forula my line graph dies exactly where it is supposed...