CODEWITHSUNDEEP
excelvba
Iain Wood
Iain Wood

Reputation: 23

How do I search a column for specific text using the If statement?

I am trying to get the code to check if a whole column contains specific text.
It will always be capitals. There is a lot of code after this line but I only want the code to run if the following condition is met. The code I am thinking along the lines of is as follows.

If ActiveSheet.Range("J:J").Text = "GENERAL" Then

However this returns nothing, I have tried Instr() but to no avail.

What is the right syntax?

Upvotes: 2

Views: 594

Answers (2)

brettdj
brettdj

Reputation: 55672

FInd has the usuage

Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat])

You should specify whether matching "GENERAL" should be the whole string (use LookAt:=xlWhole), or a partial match (ie to match GENERAL in "GENERAL COMMOTION" use LookAt:=xlPart)

Also it's generally best to use a Range object (rng1 below) so you can work with the found object. Although in your case a Boolean test will be enough to either run or not run your further code

Partial Match

Sub FindPartialString()
Dim rng1 As Range
'find partial match
Set rng1 = ActiveSheet.Range("J:J").Find("GENERAL", , xlValues, xlPart, , , True)
If Not rng1 Is Nothing Then
MsgBox "found in " & rng1.Address(0, 0)
Else
MsgBox "no found", vbCritical
End If
End Sub

Whole Match

Sub FindWholeString()
Dim rng1 As Range
'find exact match
Set rng1 = ActiveSheet.Range("J:J").Find("GENERAL", , xlValues, xlWhole, , , True)
If Not rng1 Is Nothing Then
MsgBox "found in " & rng1.Address(0, 0)
Else
MsgBox "no found", vbCritical
End If
End Sub

Upvotes: 1

Alex K.
Alex K.

Reputation: 175748

You can search within a range;

if not ActiveSheet.Range("J:J").Find("GENERAL", MatchCase:=True) is nothing then
    msgbox "found it!"
end if

Upvotes: 3

Related Questions