Excel Macro - If Cell contains a certain word then

prawln

New Member
Joined
Jul 19, 2010
Messages
6
Hey all,

Sorry im new to this site and have spent quite abit of time today trying to find out the answer to this question but cant seem to find much so i thought id ask so here goes.
So what im trying to do is use a macro with a button that once clicked on will do the following.

if cell c1 has the word "uninstall" anywhere in it e.g Microsoftworduninstallpackage sort of thing then cell c3 will clear it self and be blank.

Ive tried lots of different things but cant seem to get it right.
Im probably really off track with this (beginner at this stuff) but this is what i have

Private Sub Uninstalls_Click()
If InStr(0, (Range("c1").Value), "uninstall") > 0 Then
Range("c3").ClearContents

End If

End Sub

Any help would be so good!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
You're close. Just need to change the 0 to a 1 (the start character can only be >=1)

If InStr(1, (Range("c1").Value), "uninstall") > 0 Then

HTH
DK
 

prawln

New Member
Joined
Jul 19, 2010
Messages
6
cheers mate worked perfect, if i wanted to making it search for more then one word and do the same thing so say like "uninstall" and "software" sort of thing
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi

You could check for multiple values using something like this:

Code:
Private Sub Uninstalls_Click()

Dim sCellVal As String

sCellVal = Range("C1").Value
    
If sCellVal Like "*software*" Or _
    sCellVal Like "uninstall*" Then
    
    Range("c3").ClearContents

End If

End Sub

This code is case sensitive i.e. it will match "thesoftwaretext" but not "theSOFTwaretext". If you want to make it case insensitive then put then change the first line of code to:

sCellVal = LCase$(Range("C1").Value

HTH
DK
 

Brundles

New Member
Joined
Jun 8, 2013
Messages
7

ADVERTISEMENT

Hi DK,

What if I wanted a macro to do this for an entire column rather than just one cell?

Thanks,
B
 

Brundles

New Member
Joined
Jun 8, 2013
Messages
7
Nevermind, I was able to figure it out. Below is the code I used:

Sub SORT()
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A6:A1000")
For Each myCell In myRange
If myCell Like "*word*" Or _
myCell Like "*otherword*" Then

myCell.Font.Bold = True

End If
Next myCell
End Sub


This may not be the best code, but it accomplishes what I was looking for. In this case, I was setting the text to bold rather than deleting the cell.
 

ScanlineVFX

New Member
Joined
Mar 25, 2014
Messages
4

ADVERTISEMENT

Hi everyone I am trying to do something similar but instead of clearing a cell, populating a cell with new info to sort my companies CC statements by purchases.

IE: Cell B2 has the following info
CHECK CRD PURCHASE 02/27 FEDEX ME LOS ANGELES CA 111111XXXXXX1234 222222222222222 ?MCC=4321
Once this is input the macro will know X1234 = BRIAN and input his name into Cell E2.

This should work for the entire worksheet translating the info from column B and populating column E.

This is literally my first foray into Macro's so hold my hand on this one, still figuring this all out.

<colgroup><col></colgroup><tbody>
</tbody>
 

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
Nevermind, I was able to figure it out. Below is the code I used:

Sub SORT()
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A6:A1000")
For Each myCell In myRange
If myCell Like "*word*" Or _
myCell Like "*otherword*" Then

myCell.Font.Bold = True

End If
Next myCell
End Sub


This may not be the best code, but it accomplishes what I was looking for. In this case, I was setting the text to bold rather than deleting the cell.

Hi, I have similar problem, and problem in inserting multiple condition. Could you please help me in this..
This is my code..
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("B3:B100")
For Each myCell In myRange
If myCell Like "* - *" Then 'example= ABC 002 - 05/89
myCell.Replace " - ", Chr(1), xlPart, , , , False, False
myCell.Replace "/", "/01/", xlPart, , , , False, False
myCell.TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End If

If myCell Like "* Ed. *" Then 'example= ABC-19076 Ed. 01-16
myCell.Replace " Ed. ", Chr(1), xlPart, , , , False, False
'Here I am facing problem, after it replacing, my text will go in,
'example= ABC-19076 01-16

'and therefore, could able to use below code.
'Could you please advise me what can be the best solution.
myCell.Replace "/", "/01/", xlPart, , , , False, False
myCell.TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End If

If myCell Like "* *" Then 'example= ACF-7007 08/11
myCell.Replace "/", "/01/", xlPart, , , , False, False
myCell.TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End If
Next myCell
 

cuchi33

New Member
Joined
Aug 5, 2017
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
Hi

You could check for multiple values using something like this:

Code:
Private Sub Uninstalls_Click()

Dim sCellVal As String

sCellVal = Range("C1").Value
   
If sCellVal Like "*software*" Or _
    sCellVal Like "uninstall*" Then
   
    Range("c3").ClearContents

End If

End Sub

This code is case sensitive i.e. it will match "thesoftwaretext" but not "theSOFTwaretext". If you want to make it case insensitive then put then change the first line of code to:

sCellVal = LCase$(Range("C1").Value

HTH
DK
Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top