Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If column contains a value - Error Trap
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If column contains a value - Error Trap
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. Louise wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If column contains a value - Error Trap
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. -- Rick (MVP - Excel) "Louise" wrote in message ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set Error handling INSIDE error-trap | Excel Programming | |||
Need error trap | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error Trap | Excel Programming |