Wildcard in VBA?

I apologize if this is here twice--I don't see the post I thought went 
through earlier today.  Anyway ..

I have the following code:  

If .Cells(X, "H") = "RG" Then
           .Cells(X, "G").Value = "TRC"
End If

Can I modify this with a "wildcard" character?  In other words, if RG is 
found anywhere (RG123, RG456, 123RG, etc) within cell H, I want TRC to be 
placed into column G

Thanks!
0
Utf
1/15/2010 1:06:01 AM
excel.programming 6508 articles. 1 followers. Follow

4 Replies
3014 Views

Similar Articles

[PageSpeed] 28

maybe something like this, i just hard coded the variable for testing

Sub test()
Dim x As Long
x = 3

If Cells(x, "H") Like "?G" Then
           Cells(x, "G").Value = "TRC"
End If

End Sub

-- 


Gary Keramidas
Excel 2003


"richzip" <richzip@discussions.microsoft.com> wrote in message 
news:A86712BF-A835-41B6-8BCD-038648805CEE@microsoft.com...
>I apologize if this is here twice--I don't see the post I thought went
> through earlier today.  Anyway ..
>
> I have the following code:
>
> If .Cells(X, "H") = "RG" Then
>           .Cells(X, "G").Value = "TRC"
> End If
>
> Can I modify this with a "wildcard" character?  In other words, if RG is
> found anywhere (RG123, RG456, 123RG, etc) within cell H, I want TRC to be
> placed into column G
>
> Thanks! 

0
Gary
1/15/2010 1:33:01 AM
Try something like

If InStr(Cells(X, "H").Text, "RG") > 0 Then
    Cells(X, "G").Value = "TRC"
End If

Steve

"richzip" <richzip@discussions.microsoft.com> wrote in message 
news:A86712BF-A835-41B6-8BCD-038648805CEE@microsoft.com...
> I apologize if this is here twice--I don't see the post I thought went
> through earlier today.  Anyway ..
>
> I have the following code:
>
> If .Cells(X, "H") = "RG" Then
>           .Cells(X, "G").Value = "TRC"
> End If
>
> Can I modify this with a "wildcard" character?  In other words, if RG is
> found anywhere (RG123, RG456, 123RG, etc) within cell H, I want TRC to be
> placed into column G
>
> Thanks! 

0
Steve
1/15/2010 1:53:01 AM
Given what the OP posted, I believe your Like operator test should be =
this...

If Cells(x, "H") Like "*RG*" Then

That would be a case sensitive test; if the OP need a non-case sensitive =
test, that would look like this...

If Cells(x, "H") Like "*[Rr][Gg]*" Then

--=20
Rick (MVP - Excel)


"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message =
news:uLVUwKYlKHA.4872@TK2MSFTNGP05.phx.gbl...
> maybe something like this, i just hard coded the variable for testing
>=20
> Sub test()
> Dim x As Long
> x =3D 3
>=20
> If Cells(x, "H") Like "?G" Then
>           Cells(x, "G").Value =3D "TRC"
> End If
>=20
> End Sub
>=20
> --=20
>=20
>=20
> Gary Keramidas
> Excel 2003
>=20
>=20
> "richzip" <richzip@discussions.microsoft.com> wrote in message=20
> news:A86712BF-A835-41B6-8BCD-038648805CEE@microsoft.com...
>>I apologize if this is here twice--I don't see the post I thought went
>> through earlier today.  Anyway ..
>>
>> I have the following code:
>>
>> If .Cells(X, "H") =3D "RG" Then
>>           .Cells(X, "G").Value =3D "TRC"
>> End If
>>
>> Can I modify this with a "wildcard" character?  In other words, if RG =
is
>> found anywhere (RG123, RG456, 123RG, etc) within cell H, I want TRC =
to be
>> placed into column G
>>
>> Thanks!=20
>
0
Rick
1/15/2010 4:00:27 AM
yea, i missed this part (RG123, RG456, 123RG).
i guess i should read the entire post<g>

-- 


Gary Keramidas
Excel 2003


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:OZvB9cZlKHA.2164@TK2MSFTNGP02.phx.gbl...
Given what the OP posted, I believe your Like operator test should be this...

If Cells(x, "H") Like "*RG*" Then

That would be a case sensitive test; if the OP need a non-case sensitive test, 
that would look like this...

If Cells(x, "H") Like "*[Rr][Gg]*" Then

-- 
Rick (MVP - Excel)


"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message 
news:uLVUwKYlKHA.4872@TK2MSFTNGP05.phx.gbl...
> maybe something like this, i just hard coded the variable for testing
>
> Sub test()
> Dim x As Long
> x = 3
>
> If Cells(x, "H") Like "?G" Then
>           Cells(x, "G").Value = "TRC"
> End If
>
> End Sub
>
> -- 
>
>
> Gary Keramidas
> Excel 2003
>
>
> "richzip" <richzip@discussions.microsoft.com> wrote in message
> news:A86712BF-A835-41B6-8BCD-038648805CEE@microsoft.com...
>>I apologize if this is here twice--I don't see the post I thought went
>> through earlier today.  Anyway ..
>>
>> I have the following code:
>>
>> If .Cells(X, "H") = "RG" Then
>>           .Cells(X, "G").Value = "TRC"
>> End If
>>
>> Can I modify this with a "wildcard" character?  In other words, if RG is
>> found anywhere (RG123, RG456, 123RG, etc) within cell H, I want TRC to be
>> placed into column G
>>
>> Thanks!
> 

0
Gary
1/15/2010 4:22:30 AM
Reply:

Similar Artilces:

Wildcard in query
Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards It will not work like you have it as you are saying Like "Honda Prelude...

Convert VBA to ASP
I am considering changing my extensive VBA code database to ASP. Is this an impossible task and should I just rewrite the whole thing? Is ASP the up and coming language for database code? Is Microsoft going to be changing from VBA to something else as its main code? I think I am out of my league on this one and would appreciate some help. No, I don't think ASP is "the up and coming language for database code". ASP.Net has surplanted it already, and who knows how long it'll be before Microsoft tells us ASP.Net wasn't the correct answer either? Afraid I don'...

sumproduct with wildcards?
Hi, will sumproduct() allow the use of wildcards? I'm trying to sum a portion of a large spreadsheet. One column is numerical, the other is text. All of the entries in the text column begin with common values but differ at the end, hence my attempt at the use of a wildcard. But it doesn't seem to work. Any suggestions? thank you. jim -- "If you carve yourself to suit everybody, you'll soon whittle yourself away" No, SUMPRODUCT will not work (directly) with wildcards. You can use something like this: Sum values in column B where the values in column begin wi...

Autocorrect with wildcards
I work in a company in the UK which works with Norwegian clients a lot, often people with � in their name. I want to set up people's computers so when the type aaa in a word, it comes out with �. Eg. if they type 'Paaal' they get the name 'P�l'. When I program autocorrect to change aaa to � it only does it when aaa is typed as a word, not within a word. Is there a character I can use as a wildcard, or some other way to get autocorrect to do this? -- bluebottle There is no way to make AutoCorrect do what you propose. However, there is a default k...

Alias with wildcard character
Can I use wildcard characters (such as * for 1 or more characters) in a email address alias? For example, I would like to use the alias case_*.mydomain.com to forward to an existing email account. I'm trying to setup an issue tracking system, which wants to embed the case number into the email address. For example, all email related to case #588 will be sent to address case_588@mydomain.com and that email will be forwarded to an Exchange account. Case number can be from 1 to 6 characters, so manually creating aliases is not an option. Any help/suggestion you can provide is much ...

Cannot access macros, security settings or VBA editor in OL 2003
Hello all, I've run into a problem on 3 separate machines (of 75) where the user cannot access the macros, security settings or VBA editor in Outlook 2003. The menu items are there but clicking them has no effect. Researching this on the net there are various options. I've tried the detect and repair office and fully uninstalling and reinstalling Office to no avail. If I delete the user profile from the machine, and recreate it, it starts working again. All the machines in the office are built from an image and Office is installed via a GPO, so I'm pretty sure that the ...

wildcards
Greetings everyone I\'m trying to use wild cards inside an array. Column F contains error codes 1.1, 1.2. 1.3 etc Column I contains dates in April; 2.04.07, 5.04.07 etc... I tried this: but it doesnt work sum{(F3:F500=\"1*\")*(I3:I500=\"*4*\")} I also tried it with sumproduct and no array but still no luck. Any idea how to get the desired affect? thanks a lot Cookie boy -- Via: http://www.jumlers.com/microsoft.public.excel/ Try: =SUMPRODUCT((LEFT(F1:F25,2)="1.")*(MID(I1:I25,LEN(I1:I25)-4,2)="04")) 2.04.07 is not a date unless your date separ...

VBA to run all checkbox code
I would like to create code to put on a push button that will look at all of the check box options I have and run the code that is assigned to each check box. The code that is currently assigned to each checkbox is similar to the following: Private Sub CheckBox1_Click() Sheets("Form").Activate ActiveSheet.Range("AddingEquipment").Select If Selection.Interior.Pattern = xlNone Then With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Else With Selection.Interior .Pattern = xlNone End With End If End Sub I would like for ...

Formatting footer in VBA code
Hi all.... I have this bit of code in a macro that I would like formatted, but I do not know how....the code is to automatically insert the file path and the tab name of a spreadheet....I would like to format the code, so that every time it opens a document it will make the font Arial size 8. What I have now only makes the "&A" (which is the tab) size 8...... Here is the string.... .CenterFooter = ActiveWorkbook.FullName & Chr(10) & "&""Arial""&8&A" Any suggestions would be great!!!!!!!!!!!! I'm not sure I foll...

VBA code to paste data based on condition
I'm new to VBA programming and would appreciate some help with a macro. There is a database table that can be refreshed throughout the month that simply overrides the cell value from the same refresh. So the values update every time you refresh in the same cell until the next month begins (Date + Year are the column headings). What I need to do is break each month down into weeks like this (1-7 = Week 1, and so on where Week 5 is any day after the 28th of the month). I need a macro where the morning a new week starts (Day 8), I can copy/paste values the entire column in...

Access 2007 w/VBA
This is only a test, to see if the newsgroup "consolidates" posts with the same "Subject:" Regards Jeff B. AHA! Note to self (and any others interested): If I use a "Subject:" for a new post, and that subject is identical to a "Subject:" already used by another poster, the newsgroup grabs my post and shoves it underneath the other "Subject:" post, treating it as if it were a response. Jeff B. "Jeff Boyce" <nonsense@nonsense.com> wrote in message news:%23EhPYCH7KHA.3880@TK2MSFTNGP04.phx.gbl... >...

wildcarding recipients
Hello Group, Can someone tell me if its possible to wildcard recipients for a public folder? I have several domains on my 5.5 box (soon to be 2003), and I have a public folder setup as a black hole. I want any email sent to sales@ (to any of the domains that I have) to be picked up by this folder, and unceramoneously deleted. Can this be done, or do I have to explicitly list sales@domain.com for each domain that I have ? Thanks, NuTs You will need a third party tool for wildcarding. Look http://turbogeeks.com/products/mailbasketmd.asp You can then set Age Limits on your Public Folder...

Replacing a sentence with VBA-code
One my form includes a control to calculate amount of holes. I am using following sentence to calculate: Holes: IIf([lenhgt] Between 120 And 800;2;IIf([lenhgt] Between 800 And 1350;3;IIf([lenhgt] Between 1350 And 1900;4;IIf([lenhgt] Between 1900 And 2450;5;IIf([lenhgt] Between 2450 And 3000;6;IIf([lenhgt] Between 3000 And 3500;7)))))) Measures are as millimeter. There is another control on the form to calculate the distance of holes. Is there sense to change sentences to VBA-code? Both of sentences return right values. If so, what kind of code is needed? -- Have a Nice Day! Hi Mark...

Unlock VBA Project
I have VBA project written quite sometime ago which I now need to edit it. I still remember the password, but I can't unlock it. I got a "Project Unviewable" message. Would anyone like to help as to how to unlock it and preventing this happening again? Many thanks. Maybe you try to unlock it in older Excel version that you create the password. For example if you create password in 2000, you can't unlock in 97 -- Regards Ron de Bruin http://www.rondebruin.nl "zhj23" <zhj23@discussions.microsoft.com> wrote in message news:B88BE1AE-F684-4644-8597-33F1254...

Wildcard search
How to do wild card search for filename like c:\doc??ents\abc???\*.exe this???? Findfile will not work for this.Is there any other way to do it or any work arround?? "Dipali" <Dipali@discussions.microsoft.com> schrieb im Newsbeitrag news:FD137C20-72D8-42A8-AE57-39A1FAEEB127@microsoft.com... > How to do wild card search for filename > like c:\doc??ents\abc???\*.exe this???? > > Findfile will not work for this.Is there any other way to do it or any > work > arround?? Wildcards are not allowed in a path. You must tell FileFind where to look for files an...

VBA code to find first blank cell in a column and activate it
MS XP Pro, MS Office 2007. Can anyone assist with macro to find the first blank cell in a column and then to make it the active (selected) cell so that data can be pasted to it and the row in which it is placed. I cant find a stable and reliable solution with the Find Blank Cell procedure in the menu. When you say first blank cell, do you mean - From the top down, the first blank cell with possibly other non blank cells below that blank cell. or - From the bottom up, the cell below the first non-blank cell. Regards, Peter T "Wes_A" <WesA@discussions.microso...

Access 2007 w/VBA
Hi Folks, Looking for a way to generate a sequence number (not record id) and populate a table field. I have had no luck using a command button and VBA code but that is more the fault of my novice status with VBA and Access than limitations of either. I would be happy if I could launch a macro, via the command button, have the macro open a file that stores a variable, increment that variable by 1, and present the new value to the user who would then input it into the table element... and then store the new variable. I would appreciate any assistance/guidance you can offe...

Wildcard SMTP address delivery
How can you configure Exchange 5.5 to deliver all messages to that domain (i.e. *@email.com) to be delivered into a certain mailbox, not the administrator mailbox. thomas ...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Wildcards..
I am trying to write an IF formula that says =IF(a1="*",1,0) Where the * is absolutely anything (a number, text etc..) I presume the formula is just looking for an asterisk within the cell but I want it to pick up that the cell contains anything. Any ideas? -- Andrew ******************************************************* *** Si hoc legere scis, nimis eruditionis habes *** ******************************************************* Hi one way: =IF(A1<>"",1,0) or =--(A1<>0) >-----Original Message----- >I am trying to write an IF formula that says &g...

I am using VBA, how do you test for new record in a form
What should I test my VBA code so that I know the User is working with record as New in a Form. I am using Access 2007 under Windows XP. I used PrimaryID as an auto generated number, therefore it is Null in the begining that is how I know the user is trying to add a new record and not editing an existing one. So I test for IsNull(PrimaryID) which works if the user do not make mistake but as the user enter more information made mistakes and need to correct the PrimaryID is no longer Null. So my test passed as if the use is editing the existing record when in fact the user is...

Error while Changing Form.Filter in VBA
Hi. I have a combobox OnChange event which should update a form's datasource base on the combobox's value in Colum1. Code is below. But when this code executes I get the error "Run-time error '2001': You canceled the previous operation." Private Sub FilteredContacts_Change() Me.Form.Filter = "[Contact_ID] = '" & FilteredContacts.Column(1) & "'" Me.Form.FilterOn = True End Sub What am I doing wrong or how do I fix this? Thanks! That very misleading error message often occurs when you've misspelled the name of a...

Help with wildcard Replace syntax, please?
I tried a wildcard Find/Replace: -- Find: 5 spaces, any combination of 2 numbers, a period ( {5,})[0-9]{2,}. -- Replace: 9 spaces, then "a." ( {9})a. Find worked. Replace gave me: " {9}a." I gave up trying to code it and just typed 9 spaces! 8>/ Is there something special I needed to do in the Replace box? Ed You must enter into the Replace box exactly what it is that you want to be used as the replacement Though, you could have used ([ ]{4}) [0-9]{2} in the Find what control and then \1\1 a in the Replace with control -- Hope this...

Open New Visio Drawing using VBA in Word?
Hi, We're creating new Word 2007 templates, and some of them require flowchart functionality from Visio. Our goal is to create a macro within the Word template to call Visio, open a new drawing and have a custom stencil set open for users to use. Users would then open create their flowchart/diagram in Visio, and it would update automatically into the Word template when Visio is closed. I'm using the following code to start Visio, however I'm stuck on where to go from there: Sub Visio() Shell "C:\Program Files\Microsoft Office\Office12\VISIO.exe", vbN...

VBA in IE window
Hi Im accessing a spreadsheet via a link I have put in a web page. The spreadsheet opens up in an IE window. I am wanting to stop the "save changes" dialog box popping up when I close the window, I cant do this using the usual vba method because it doesnt apply to IE. Would like to exit using an "exit" command button, Any idea how to close the active IE window using VBA? Am also wanting to open it in full screen (compensate for the lack of a full screen command for "taget_blank" in HTML) Any suggestions gratefully received. Try something like t...