CODEWITHSUNDEEP
excelexcel-2007vba
Subhaac
Subhaac

Reputation: 437

Finding a string in a cell

I'm trying to check if the string NAME is present in an active cell, and if it is present, the program should ignore the cell. If not present, then that cell's contents must be deleted. This is the code I have:

Sub Search()

    Range("B3").Select
    If ActiveCell.Find(What:="NAME") = False Then ActiveCell.Clear

End Sub

I don't understand why it doesn't work. As you can see, I'm just checking cell B3 for now.

Upvotes: 1

Views: 4858

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

It all depends on what kind of match you are trying to find. So what are you trying to find? A complete match? A partial match? A case sensitive match?

A) Complete Match + Case Sensitive

In such a case the cell value has to be just "NAME". Instances like " NaMe", "NAme", "name" etc will not be matched.

If ActiveCell.Value <> "NAME" Then ActiveCell.ClearContents

B) Complete Match + Not Case Sensitive

If UCase(ActiveCell.Value) <> "NAME" Then ActiveCell.ClearContents

or

If LCase(ActiveCell.Value) <> "name" Then ActiveCell.ClearContents

In such a case the cell value has to be "NAME" or "NaMe" etc. Instances like " NaMe", "Surname" etc will not be matched.

C) Partial Match + Case Sensitive

If InStr(1, ActiveCell.Value, "NAME") = 0 Then ActiveCell.ClearContents

In such a case the cell value must contain "NAME". For example NAME GAME. SURNAME etc. Instances like "My Name is Sid", "What's in a NAMe" etc will not be matched.

D) Partial Match + Not Case Sensitive

If InStr(1, ActiveCell.Value, "NAME", vbTextCompare) = 0 Then ActiveCell.ClearContents

In such a case the cell value must contain "NAME". The Case doesn't matter. For example NAME GAME, SURNAme, "My Name is Sid", "What's in a NAMe" etc.

Here is an snapshot of different scenarios.

enter image description here

Note: If you want to use .FIND then see THIS LINK. Based on the above scenarios, you will have to play with

  1. LookAt:= which can take xlPart or xlWhole as one of it's arguments.
  2. MatchCase:= which can take True or False as one of it's arguments.

Upvotes: 3

pnuts
pnuts

Reputation: 59460

Does this do what you would like it to?: 

Sub Search()
   Range("B3").Activate
    If ActiveCell.Value = "NAME" = False Then ActiveCell.Clear
End Sub

Note, is case sensitive and looks for the entire cell's content rather than a string within a cell.

Upvotes: 2

Related Questions