If column contains a value - Error Trap

[Louise]

Hi,

I am trying to apply some error trapping to my code now.

I want to say that if a value (e.g. "Test") does not appear in Column A then
carry on, otherwise if it does appear in Column A (on any row) then an error
message pops up.

Can this be done, and how?

Thanks,

 

[Dave Peterson]

Dim HowMany as long

with worksheets("Somesheetnamehere")
howmany = application.countif(.range("a:a"),"test")
if howmany > 0 then
'it's there at least once
else
'it's not there
end if
end with

You may want:

howmany = application.countif(.range("a:a"),"*test*")

The asterisks are wildcards and you'll find that Test anywhere within the cell
(along with other characters).

======
Another option...

Dim FoundCell as range
with worksheets("somesheetnamehere")
with .range("a:a")
set foundcell = .cells.find(what:="test", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with
end with

if foundcell is nothing then
'not found
else
'found
end if

You may want xlPart instead of xlWhole and you may want to match the case, too.
(And/or look in xlFormulas, too.)

Untested, uncompiled. Watch for typos.

 

[Rick Rothstein]

There are a few ways you can structure this depending on what you want to
happen after the error message is displayed, but the following should get
you started...

If Not Columns("A").Find("Test", LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "Here is your error message"
Else
MsgBox "Put your working code in here."
End If

The key is to perform the "Not...Is Nothing" test on the result of
attempting to Find the text you are looking for in the specified range for
the Find method.