if/then returns false instead of blank cell

If I put this formula into a cell it works fine and displays a blank cell if
A18 is blank, otherwise it displays cell A18:
If(A18="","",A18)

If I put this formula into vb script (a click action on a button)the cell
now displays FALSE as the value, instead of a blank cell:

With ActiveSheet
            .Name = "Pay App 1"
            .Range("O11").Formula = "=If(A18="","",A18)"
            .Range("O13").Formula = "=If(A19="","",A19)"
            .Range("O15").Formula = "=If(A20="","",A20)"
            .Range("O17").Formula = "=If(A21="","",A21)"
            .Visible = True
            .Range("A3").Select
        End With

My question is how do I get the vb code to display the cell as blank,
instead of displaying the word FALSE?
I notice that when the code runs the formula it enters into cell O11 is:
If(A18=",",A18)". This is missing quotes and not how I typed it above.
Anyway to display the cell blank, instead of the word false?
Thanks.


0
software1 (25)
10/14/2003 6:46:29 PM
excel 39879 articles. 2 followers. Follow

4 Replies
563 Views

Similar Articles

[PageSpeed] 13

On Tue, 14 Oct 2003 18:46:29 GMT, "purplehaz" <software@for.me> wrote:

>If I put this formula into a cell it works fine and displays a blank cell if
>A18 is blank, otherwise it displays cell A18:
>If(A18="","",A18)
>
>If I put this formula into vb script (a click action on a button)the cell
>now displays FALSE as the value, instead of a blank cell:
>
>With ActiveSheet
>            .Name = "Pay App 1"
>            .Range("O11").Formula = "=If(A18="","",A18)"
>            .Range("O13").Formula = "=If(A19="","",A19)"
>            .Range("O15").Formula = "=If(A20="","",A20)"
>            .Range("O17").Formula = "=If(A21="","",A21)"
>            .Visible = True
>            .Range("A3").Select
>        End With
>
>My question is how do I get the vb code to display the cell as blank,
>instead of displaying the word FALSE?
>I notice that when the code runs the formula it enters into cell O11 is:
>If(A18=",",A18)". This is missing quotes and not how I typed it above.
>Anyway to display the cell blank, instead of the word false?
>Thanks.
>

Not enough quotes.  If you want to use a quote inside a quote, you need to
double up on it.

ith ActiveSheet
            .Name = "Pay App 1"
            .Range("O11").Formula = "=If(A18="""","""",A18)"
            .Range("O13").Formula = "=If(A19="""","""",A19)"
            .Range("O15").Formula = "=If(A20="""","""",A20)"
            .Range("O17").Formula = "=If(A21="""","""",A21)"
            .Visible = True
            .Range("A3").Select
        End With


--ron
0
ronrosenfeld (3122)
10/14/2003 7:22:32 PM
Hi

Very simplified; a doublequote " in VBA means "end of text" . To get one actual double
quote you muet use two: "" . And since you need two "" then you must write four: """" .

"=If(A18="""","""",A18)"

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"purplehaz" <software@for.me> wrote in message
news:9wXib.53894$uA2.12637@twister.nyroc.rr.com...
> If I put this formula into a cell it works fine and displays a blank cell if
> A18 is blank, otherwise it displays cell A18:
> If(A18="","",A18)
>
> If I put this formula into vb script (a click action on a button)the cell
> now displays FALSE as the value, instead of a blank cell:
>
> With ActiveSheet
>             .Name = "Pay App 1"
>             .Range("O11").Formula = "=If(A18="","",A18)"
>             .Range("O13").Formula = "=If(A19="","",A19)"
>             .Range("O15").Formula = "=If(A20="","",A20)"
>             .Range("O17").Formula = "=If(A21="","",A21)"
>             .Visible = True
>             .Range("A3").Select
>         End With
>
> My question is how do I get the vb code to display the cell as blank,
> instead of displaying the word FALSE?
> I notice that when the code runs the formula it enters into cell O11 is:
> If(A18=",",A18)". This is missing quotes and not how I typed it above.
> Anyway to display the cell blank, instead of the word false?
> Thanks.
>
>


0
innocent (844)
10/14/2003 7:30:14 PM
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:r5joovcvprup4appokh0b4liio35o3thhf@4ax.com...
> On Tue, 14 Oct 2003 18:46:29 GMT, "purplehaz" <software@for.me> wrote:
>
> >If I put this formula into a cell it works fine and displays a blank cell
if
> >A18 is blank, otherwise it displays cell A18:
> >If(A18="","",A18)
> >
> >If I put this formula into vb script (a click action on a button)the cell
> >now displays FALSE as the value, instead of a blank cell:
> >
> >With ActiveSheet
> >            .Name = "Pay App 1"
> >            .Range("O11").Formula = "=If(A18="","",A18)"
> >            .Range("O13").Formula = "=If(A19="","",A19)"
> >            .Range("O15").Formula = "=If(A20="","",A20)"
> >            .Range("O17").Formula = "=If(A21="","",A21)"
> >            .Visible = True
> >            .Range("A3").Select
> >        End With
> >
> >My question is how do I get the vb code to display the cell as blank,
> >instead of displaying the word FALSE?
> >I notice that when the code runs the formula it enters into cell O11 is:
> >If(A18=",",A18)". This is missing quotes and not how I typed it above.
> >Anyway to display the cell blank, instead of the word false?
> >Thanks.
> >
>
> Not enough quotes.  If you want to use a quote inside a quote, you need to
> double up on it.
>
> ith ActiveSheet
>             .Name = "Pay App 1"
>             .Range("O11").Formula = "=If(A18="""","""",A18)"
>             .Range("O13").Formula = "=If(A19="""","""",A19)"
>             .Range("O15").Formula = "=If(A20="""","""",A20)"
>             .Range("O17").Formula = "=If(A21="""","""",A21)"
>             .Visible = True
>             .Range("A3").Select
>         End With
>
>
Thanks alot, that was it. I'm still new to writing the formulas by hand in
vb, but learning more everyday.


0
software1 (25)
10/14/2003 9:02:23 PM
Thanks alot, that was it.

"Harald Staff" <innocent@enron.invalid> wrote in message
news:eRw%23pkokDHA.3688@TK2MSFTNGP11.phx.gbl...
> Hi
>
> Very simplified; a doublequote " in VBA means "end of text" . To get one
actual double
> quote you muet use two: "" . And since you need two "" then you must write
four: """" .
>
> "=If(A18="""","""",A18)"
>
> --
> HTH. Best wishes Harald
> Followup to newsgroup only please.
>
> "purplehaz" <software@for.me> wrote in message
> news:9wXib.53894$uA2.12637@twister.nyroc.rr.com...
> > If I put this formula into a cell it works fine and displays a blank
cell if
> > A18 is blank, otherwise it displays cell A18:
> > If(A18="","",A18)
> >
> > If I put this formula into vb script (a click action on a button)the
cell
> > now displays FALSE as the value, instead of a blank cell:
> >
> > With ActiveSheet
> >             .Name = "Pay App 1"
> >             .Range("O11").Formula = "=If(A18="","",A18)"
> >             .Range("O13").Formula = "=If(A19="","",A19)"
> >             .Range("O15").Formula = "=If(A20="","",A20)"
> >             .Range("O17").Formula = "=If(A21="","",A21)"
> >             .Visible = True
> >             .Range("A3").Select
> >         End With
> >
> > My question is how do I get the vb code to display the cell as blank,
> > instead of displaying the word FALSE?
> > I notice that when the code runs the formula it enters into cell O11 is:
> > If(A18=",",A18)". This is missing quotes and not how I typed it above.
> > Anyway to display the cell blank, instead of the word false?
> > Thanks.
> >
> >
>
>


0
software1 (25)
10/14/2003 9:04:04 PM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

How do I count the number of cells
I have several dozens columns and 52 rows containing Data. All the columns are labled Odd or Even In the final (right most) Column I want to count the number of times that that coulmn has data in it The last row belows shows what the answer should be Total Total Odd Even Odd Even Odd Even Odd 5 1 2 ????? ????? 1 7 6 3 ????? ????? 5 1 2 ????? ????? 1 2 9 3 1 3 PLease help =COUNTA() - will count the number of non blank cells. In the brackets place the range name (for example A1:A2). Please hit Yes if my comments have helped. Thanks. &...

Dynamic unlock a cell (under condition)
Hello guys, lets see if anyone could help me out on how to do this: I have a sheet protected with a password, the reason of this (you know) is that there are some cells that are locked (to not see their formulas or 'cause I don't want them to be changed under any circustances). This is working fine, but now I have the need of some cells (two ranges actually, these are E7:E56 & N7:N56) that they come locked by default but under certain conditions I would need them to get unlocked. Taking an E7 as example, let's say that depending on what we have in B7 this E7 will chang...

Remove Cell Auto-Format (General) on Paste / Replace
Good morning all, Just curious if this is able to be done. I have a series o spreadsheets, all of them do different things, but the common factor i that I paste claim numbers into them. The problem is, Excel seems t think our claim numbers are dates. (Example. 2146/04 or 04/262 (different formats for different sections). Now, I have formatted AL cells on these spreadsheets as text. (Ctrl-A, Format-Cells-Text However, when pasting, Excel overrides whatever you've got, and choose it's own way to paste data. The other problem is with Find-Replace or Find-Replace All. A lot o the time, ...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Format cells unavailable
I am assisting a customer running Word 2000 on a Windows 2000 system. He has a spread sheet that will not allow him to do a format > cells on the header row and a few rows below. We can select the colum and this option works but nothing happens when using this option for an individual cell. I don't think these items are protected, is there any way I can be sure? I tried selecting all of the columns and unlocked the cells and this did not help. Can anyone think of any reason why format cells would not be available on this sheet? TIA George Tools, Protection. If the sheet's ...

Convert VLOOKUP to absolute cell reference
My spreadsheet has a VLOOKUP through 44,000 rows that only needed to be performed once. The relevant data is in the VLOOKUP cells as I want them and the lookup values will never change. As long as I keep the other worksheet intact, can I convert the VLOOKUP formulas to absolute references? My spreadsheet is running very slow with the VLOOKUP in place, so I'm trying to speed things up. Would this idea work? Hi Rich, You can try to convert all the formulas as values by using copy and paste special values. also you can try to go to options calculation and change the options to manu...

Comments in Cells
Is there a way to show the comments against a cell when printing a worksheet out? Regards Ric You can print the comments as they display on the worksheet, or at the end of the worksheet. There are instructions in Excel's Help, and here: http://www.contextures.com/xlcomments01.html#Printing Ric wrote: > Is there a way to show the comments against a cell when printing a worksheet > out? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:407D1E25.407...

#Error when blank
When OwnerLastName is blank I am getting an #Error can it just be blank...Thanks Bob =UCase(Left(Nz(DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] = " & [tbOwnerID] & ""),""),3)) Is OwnerID a Number field, or a Text field? Also, is this on a form or a report? The Criteria is not correct. Ignoring the Left() and UCase() until you get it working, try this if OwnerID is a Number field: =DLookUp("[OwnerLastName]","tblOwnerInfo","[OwnerID] " = Nz([tbOwnerID],0)) Or, for a Text field: =DLoo...

Ten Key Calculator cell format
Is there a way to format a cell to move the decimal back two spaces similar to a ten key adding machine. In example, if I key 1025 in a cell can a custom format make that value 10.25. Other examples: Enter Format 10 .10 456 4.56 2000 20.00 Thanks in advance for anyone who could help me with this. Manny ...

New blank workbook (Author info, colour scheme)
Hi, I found a bewildering three locations for Book.XLT on my system: "C:\Program Files\Microsoft Office\Office10" "C:\Program Files\Microsoft Office\Office10\XLStart" "C:\Documents and Settings\vnagubadi\Application Data\Microsoft\Templates" I edited the Properties/Summary/Author field of each of these Book.XLT files to indicate their file location. That way, when I created a 'New Workbook', I could quickly and easily identify which template Excel was using to generate the new file. I was ready to run some tests. (These are all in Excel XP (2002), btw) ...

Move cell info and info in range of cells on new entry
I would like to enter info into exsiting cell and when I hit enter the existing info would move down one row. B C D E 5 Enter new info her existing info moves down 6 7 Thanks for the help. -- JoAnn You could use a worksheet_change event macro within your sheet module to do this when you enter a value into the last column of the existing row. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "abc" <abc@discussions.microsoft.com> wrote in message news:8378874C-D0D0-4E73-98F6-F48E61B60898@microsoft.com......

displaying a comment only when the cell is selected
I'd like to insert a comment in a certain cell, so that when that cell is selected by a user, the comment is then displayed. I know this must be possible, but I haven't been able to figure it out.... I can show or hide comments in various combinations, but can't find a way to hide the comment UNTIL the cell is selected, and then display it. How do I do this please?! Thanks Hi! Use Data Validation. Select the cell Goto Data>Validation Select the Input message tab Type in your message OK out The message will only be displayed when that cell is selected. (no mouse ov...

Application.DisplayAlerts = False
I'm trying to make it so when I do a "saveas", the computer won't ask m "are you sure you want to overwrite?" The problem is.. I'm loading the excel sheet on internet explorer, an it's not working in this case. Anyone have any tips -- Message posted from http://www.ExcelForum.com ...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Use tiltle in a cell
Is there a way to use the title of the spreadsheet as a cell value? I asked this before in another topic, but there was no reply. My sheet get's it's title from a database and is variable. So if i can use the title i can strip several characters from the title and use this. So I want to retrieve the title of the spreadsheet and use this in a cell. Thanks! The name of a worksheet can be extracted to a cell with: =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from em...

Merge/consolidate text cells based on unique keys ?
Hi all. I hope someone can help me out. I have an Excel worksheet with 2 columns: 'Client #' and 'Invoice #'. Every time the accounting dept. generates an invoice, a new row is added in this worksheet. Obviously this is chronological not per Client #. But for the sake of simplicity, let's assume the worksheet is already sorted by Client #, like so: A B Client # Invoice # 231 5929 231 4358 231 2185 231 6234 464 1166 464 1264 464 3432 464 1720 464 9747 791 1133 791 4930 791 5496 791 6291 989 8681 989 3023 989 7935 989 8809 989 8873 My goal is to achieve...

Outlook 2003
I have a user who is reporting that whilst creating a new mail/ replying to a mail, it will occassionally blank out all the text in the mail. If he minimizes or maximizes the mail he can breifly see all the text he has typed, but it dissapears straight away. I've not managed to recreate teh problem myself, but am hoping this is a general issue with a simple answer. Thanks in advance. ...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Linking Dialog box content with cells in "regular" sheet
How to link data in Edit box (from Dialog caption) with exact cells in "regulal" Sheets? Or is there another way to link exact content from Dialog sheet to normal sheet? For example, if I have number 200 in Dialog, I need that exact number 200 on another ("regular") sheet to preform calculations with it. thnx ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

setting to have email saved on Exchange server instead of PST
Hi, In Outlook 2007, is there a setting to have all the email saved to an Exchange Server rather than to a PST file? Thanks Steve Set the default delivery location to the exchange acct in tools, account settings. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup by visiting http://w...

Replace empty cell with cell on right
Hi all I wish I could write macros!! I have a spreadsheet of names and addresses (name, add1, add2, add3, add4, add5). I want to mailmerge them but some add2s are empty, because whoever typed the data in put the next line in add3, and some add3s are empty because they used add4, and some add4s are empty etc etc This means that the mailmerge (into Publisher - yuk!) puts blank lines into the address. I would like (please!) a macro that, if I select the range add1-add5, will squash the details to the left - so all of the empty cells are on the right! Thanks. -- Andy. Good news - you don&#...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...