Excel Macro - If Cell contains a certain word then

[prawln]

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!

 

[dk]

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]

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]

Hi

You could check for multiple values using something like this:

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]

Hi DK,

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

Thanks,
B

 

[Brundles]

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]

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]

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]

Hi

You could check for multiple values using something like this:

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