How can I use wildcards in IF statements and Macros

I want to include in a Macro, something to take a particular action if the 
active cell contains text beginning with X. I thought something based on an 
IF statement, that included a wildcard, might work, e.g. IF(A1="X*,1,0). This 
does not work though.
0
Utf
11/22/2009 10:22:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1696 Views

Similar Articles

[PageSpeed] 9

Sub JustDoIt()
Dim s As String
s = ActiveCell.Value
If Left(x, 1) = "X" Then
    MsgBox "We should do something"
End If
End Sub

-- 
Gary''s Student - gsnu200909


"AlanF" wrote:

> I want to include in a Macro, something to take a particular action if the 
> active cell contains text beginning with X. I thought something based on an 
> IF statement, that included a wildcard, might work, e.g. IF(A1="X*,1,0). This 
> does not work though.
0
Utf
11/22/2009 10:26:01 PM
If you meant that you wanted to include this in a formula (not a macro), then:

=if(left(a1,1)="x",1,0)
or since you're returning 1 or 0:
=--(left(a1,1)="x")

Depending on what you're doing, you may have alternatives.  

If I wanted to count the number of values in A1:A10 that started with an X, I
don't need to use an intermediate helper cell with a formula.

Instead, I could use:
=countif(a1:a10,"X*")
or just to show how to separate the characters:
=countif(a1:a10,"X" & "*")
or if you point to a different cell:
=countif(a1:a10,B1 & "*")

=sumif() can also use wild cards.

AlanF wrote:
> 
> I want to include in a Macro, something to take a particular action if the
> active cell contains text beginning with X. I thought something based on an
> IF statement, that included a wildcard, might work, e.g. IF(A1="X*,1,0). This
> does not work though.

-- 

Dave Peterson
0
Dave
11/22/2009 11:08:28 PM
Reply:

Similar Artilces:

How can I change default setting in Office start-up?
I need to change the default setting on my Word, Excel and PPT programs (MS Office 2004) so when they open they aren't at, for example, Word 11.3.0, rather at Word 11.2.3. I mac OSX, a G5. When I do a control click on any of those programs, go to "get info" and then choose the 11.2.3 that I want to open with, it does it no problem. When I click "change all" it doesn't change the default -- the 11.3.0 is always the default despite that I'm trying to change all. How do I fix this? BTW this whole problem started when someone at the genius bar deleted one of the...

If statement- formula
Well I'm stumped again. If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999 thanx, ~Julz Hi what do you mean with 'enter contents'?. Do you want to add them? If yes try =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199 99,"<>xyz",close!ae2:ae19999) -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Well I'm stumped again. > > If close!ag2:ag19999 contains "xyz" enter contents of > close!ag2:ag19999, otherwise enter cont...

Windowsupdate using Intranet?
Is there a way to setup a local server to use as the wundowsupdate site for a large # of pc's, but that has a small pipe to the internet? Try downloading the Software update services onto one server, use that the install onto PC's etc, using Group Policies cheers >-----Original Message----- >Is there a way to setup a local server to use as the >wundowsupdate site for a large # of pc's, but that has a >small pipe to the internet? >. > ...

Easy to use recovery guide?
I am writing up my exchange 2003 recovery plan. Just wanted to know if someone came across something better than what Microsoft has? I use the built in MS backup utility that dumps my 44 + gigs to a NAS. Thanks the DR whitepaper really covers it all...ensure you are getting good backups by doing a restore to an RSG once in awhile...there's also a great tool available for problems that can come up... http://www.microsoft.com/downloads/details.aspx?familyid=C86FA454-416C-4751- BD0E-5D945B8C107B&displaylang=en good utility to have available in times of crisis... -- Susan Conkey ...

Sorting algorithms used in Excel
Just curious to see if anyone knows what algorithms the Excel programmers used to perform the sorting routines in Excel. Thanks Mark >-----Original Message----- >Just curious to see if anyone knows what algorithms the >Excel programmers used to perform the sorting routines in >Excel. > >Thanks > >Mark >. > Well, I don't know for sure, but I would guess that they use the same ones everyone else uses, converting the characters to their character set numbers and sorting them in numeric order by character set number if case sensitive, or adding a con...

Can categories be backed up somehow?
I back up rules, etc., and got to wondering if there was a file that saved our custom categories list. TIA. StargateFan <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote: > I back up rules, etc., and got to wondering if there was a file that > saved our custom categories list. TIA. Start with this: http://www.slipstick.com/outlook/olcat.htm -- Brian Tillman On Fri, 7 Jul 2006 09:27:16 -0400, "Brian Tillman" <tillman1952@yahoo.com> wrote: >StargateFan <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote: > >> I back up rules, etc., and...

Can't open .pub files either
I have installed / unistalled / installed / uninstalled Publisher 2003 several times and have the same problems others seem to have. I can create and save a .pub file but cannot open them. Get the "cannot find file" message followed by the "cannot open" message. I have loaded the same Publisher program on another computer in our office and it works fine. To save time I should said ahead of time that we: DO NOT use Norton Antivirus DO use Norton Internet Securities which have been set to "allow scripts" Please help! If I cannot re-open and re-edit .pub fi...

using button to clear contents
I have finally found out how to add a button but now I am haveing a problem on having the button clear contents in certain cells does anyone know how to do this thanks for your help I do not know any codes for this I'll bet you could learn very quickly by using the macro recorder while you select some cells and edit>clear>contents. Here is a sample for a button from the Control Toolbox Private Sub CommandButton1_Click() ActiveSheet.Range("certain cells").ClearContents End Sub "certain cells" would be your range of cells. Gord Dibben MS Exc...

can't read emails
When I do send & recieve it look like the mails go somewhere else but inbox, where they go? how can I see them? I know for sure those email where download from my server On Tue, 03 May 2005 07:34:07 -1000, emilio <emilio@discussions.microsoft.com> wrote: > When I do send & recieve it look like the mails go somewhere else but > inbox, > where they go? how can I see them? I know for sure those email where > download > from my server What version of Outlook are you running and what mode (Corp/Workgroup, Internet Mail Only, etc.) do you have it installed i...

can I print selected cells only within a worksheet
I have designed a simple template using text only for a unique advice note. I want to be able to add variable information within this template but need only for the variable information to print onto a pre-printed advice note. I do not want to hide the template as I need to see exactly where to insert the variables but I do not want it to print. Anyone help? Thanks. Maybe you can use this ? http://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruin http://www.rondebruin.nl "martin hanley" <martin hanley@discussions.microsoft.com> wrote in message news:AA062677-3...

Using icon sets with relative references
I am using Excel 2007 in the Vista OS. I am trying to use conditional formatting with an icon set in a column but Excel will not allow relative references. Example: A1=10 min B1=20 max C1=30 oh D1=a formula { =IF(C1=0,B1,(B1-C1)) } The condtional formatting of D1 would be: if the value showing in D1 is >B1 the cell would show a red 'X', if the value showing in D1 is <A1 the cell would show a green 'check mark', and if the value showing in D1 is between the values in A1 and B1 it would show a yellow 'exclamation point'. I can get the D1 ...

Show data used and percent label
I am using a horizontal bar chart to show the percent of females in the workforce. The formula total is showing as the data label, but I also need to show the composition of the formula. for example, there are 7 employees and 1 of them are female which tells me that 14.3 percent of my workforce are women. I would like to show (1/7) inside the bar and 14.3 outside of the bar. I have to reproduce this chart 28 times and there are 12 groups being analyzed on each chart. Is there a way not to have to put this in manually by placing a text bar inside of each bar? "Desiree" <...

Excel Data Querry using criteria from Cell
Hello, I have created a data query in Excel using DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY and then selecting Access and browsing to an query within an Access database. This query returns all data into excel, but I would like to limit the criteria based on what a user has typed into a cell in the Excel workbook. I thought this could be accomplished by clicking DATA - IMPORT EXTERNAL DATA then PARAMETERS but the PARAMETERS option is grayed out. I can select EDIT CURRENT QUERY, but this just brings me into the Microsoft query, where I don't see any options to query based on...

how can I split a single cell diagonally in Excel 2000
Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...

Using external email address for AD user
We have certain users that we would like to use an external email address for and give access to resources in the domain. The only solution I have found so far is to create an account they will use for access and then to add a contact so we can send emails and add as a member of DL's. Ideally, I would like to do this without adding 2 separate objects in AD. Is that possible? Hi there, Are you aware that you can create a user and mail-enable them (not mailbox-enable), establishing an external address on the account. It removes the need for both a contact and a user accoun...

HOW IS F3 USED WTH EXAMPLE
hi guys reply me soon 1) In A1 type a number such as 12% 2) With A1 still selected, in the Name box (box to left of Formula Bar) type the word taxrate and press Enter. You must press Enter for this to work! 3) Let's confirm that you have named the cell A1: click on cell D1, now click on cell A1. The name box should not display "taxrate". If not, repeat steps 2 4) In A2 type a number like 100 5) In B2 type =A2* and then tap the F3 key. A dialog box opens, select the entry "taxrate" and click the OK key. Another way is to double click the "taxrate" entry....

Predicting new Ys given new Xs using known relationship for X and
Hello. I know people have posted similar questions about using an existing relationship between X and Y to predict new Ys given new Xs but I think mine has a slightly different twist. I would appreciate any help that could be offered. I have 20 data points for both X and Y. Each X point represents the number of workers on a farm for a particular year over a 20-year period. Each Y point represents the amount of crop harvest each year over that same period. The data is from 1901 to 1920. When I plot the points and fit an exponential trend line I get a high R-square of .9 . I would li...

Can you change the way quick parts and/or auto text displays?
Is there a way to change the look of the drop down menu for quick parts and auto text so it does not take up so much space? Something like it looked in 2003? Assuming that you have discovered the building block organizer - see http://gregmaxey.mvps.org/Buiild_Employ_Custom_BB_Gallery.htm Like 2003 it ain't! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<&...

Can publisher 2003 be installed on 2 computers?
I have a office computer and home computer. I will be purchasing Publisher 2003, but can I install them on both of my computers? campfig wrote: > I have a office computer and home computer. I will be purchasing > Publisher 2003, but can I install them on both of my computers? Technically, Publisher 2003 can be installed on a desktop and a mobile computer (ie laptop). When you go to activate your second computer it just depends on whether or not you tell them it's a laptop. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com Windows Marketplace Moderator This...

transfer contacts using email
Is it possible to transfer contacts from one machine to another using email? If, yes, what does the receiving person need to do? Many thanks, Frank FJB <FJB0623@aol.com> wrote: > Is it possible to transfer contacts from one machine to another using > email? If, yes, what does the receiving person need to do? Create a new PST (File>Net>Outlook Data File). Now, right-click Contacts and choose Copy. SPecify the new PST as the destination. When the copy completes, right-click the new PST and choose Close. Close Outlook. Rename the PST's file extension to something...

Can't get Publisher to give "send as message" as an email option
We use Windows live Mail which won't work with publisher. So for the purposes of sending out an email I changed the default program to Windows mail But, under the option "send email" only the send as attachment option appears. I have changed the default program in Vista to use windows mail. Help ...

Using Dependents
In a particular instance when I am using the dependents search facility, a small symbol like a table or an Excel sheet appears, (the dependent arrow pointing to it). It's as if it is indicating that some other sheet has data dependednt on the cell. But I can't find any other sheet with dependent data. Double-click the arrow to the sheet symbol and Excel will display the dependent cells -- Kind regards, Niek Otten Microsoft MVP - Excel "Zembu" <Zembu@discussions.microsoft.com> wrote in message news:352631A7-0BF4-451D-8781-669C26270BF6@microsoft....

How can I add a CDaoRecordview and/or CDaoRecordset class to a project in VC2003?
Hello group I have a problem. I don't know how add CDaoRecordset or/and CDaoRecordview class to a project in VC2003. In VC 6 I know,but with VC2003 I do: 1. Click Project. 2. Add Class 3. Click Category/MFC -> MFC class 4. I don't see the CDaoRecordset or/and CDaoRecordview class in base class list. Please anybody, help me!!! Thank's in advance ...

Rules / Macro? Help
This is a multi-part message in MIME format. ------=_NextPart_000_0042_01C478A8.C4D615E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello. This is what I would like to do. If the subject line of the e-mail I = just received reads " Cat " then I would like Outlook to automatically = send/forward the " Cat Template ". If the subject line of the e-mail I = just received reads " Dog " then I would like Outlook to automatically = send/foard the " Cat Template ". I've looked into trying to ...

Muting Macros
I have several macros which, upon execution, flash updates to the screen with blinding speed, displaying the results of the commands. How can I inhibit this window chatter once the macros are de-bugged? -- Gary's Student Hi Gary's, > How can I inhibit > this window chatter once the macros are de-bugged? > Application.ScreenUpdating=False 'Your code Application.ScreenUpdating=True Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com First, Application.ScreenUpdating = False '<Your code here> Application.ScreenUpdating = True Secon...