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,
0
Utf
12/7/2009 2:52:02 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1134 Views

Similar Articles

[PageSpeed] 25

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
0
Dave
12/7/2009 3:28:22 PM
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" <Louise@discussions.microsoft.com> wrote in message 
news:F9D2F200-FFC2-4EB3-8D27-1EF0D014339F@microsoft.com...
> 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, 

0
Rick
12/7/2009 3:30:04 PM
Reply:

Similar Artilces:

Charting using Stacked Column Graph
Excel 2002... I think I posted this in the wrong place before. I'm trying to chart a series that is comprised of 5 - 3 number groups. I can chart the first group, but when I try to add the next series, it stacks them onto the existing columns rather than make 3 new ones. The data looks like this (simplified): A1 A2 A3 B1 B2 B3 1-10 .01 .03 .58 .023 .025 .023 11-15 .012 .025 .025 .024 .588 .280 16-20 .012 .255 .158 .024 .254 .241 I get a nice chart with A1-A3...but can't add B1-B3 next to it. Help me, please. Thanks, Gre...

Sum values on monthly base
Hello, Can someone help me with this problem: A B 1/1/2006 152.3 3/1/2006 156.2 8/1/2006 186.1 5/2/2006 185.2 19/2/2006 145.2 ... .. I need to sum from all data on a monthly base. Jan : .. Feb : ... March : ... I already tried with a new column C who has the month number and then using the "Sum. If" function. But can it be done without a extra column just 1 formula per month? Thx in advance. ...

optional column required in datasheet view
I have a document library with two content types. I have changed all columns for both content types to optional. One content type is link to a document, for which URL is required by default. I have changed it to optional. The other content type is document. URL is not used in that content type. When I bulk edit items in datasheet, though, I can't save my changes without putting data in the URL column for documents, even though the column is not used in that content type and is set as optional. I even tried making URL optional at the site content type level and the datashee...

Search columns on multiple worksheets
In a workbook I have a worksheet for each month of the year. How would I be able to automatically determine whether the entry currently being made has been entered in the same column previously on any of the worksheets? (Similar to “Find” under Edit) If so, would it be possible to indicate the previously entered data by highlighting each duplicate cell? I suggest you start with Chip's site www.cpearson.com which has lots about duplicates Then come back with more detailed question best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps fro...

getting the x-axis value through VBA
Hi All, I am facing some peculiar problem. I need to find the first x-axis datapoint value from the chart through VBA. That is finding the categoryvalue of datapoint for x -axis. Is their any function avilable in VBA. Thanxs in advance Syam It works like this: Sub GetXValue ' declare a variant to hold the array of X values Dim XVals as Variant ' declare another variant to hold the category Dim X As Variant ' populate the array XVals = ActiveChart.SeriesCollection(1).XValues ' get the value you need X = XVals(1) ' output the value Debug.Print...

Text to columns #2
Is there a way to have a worksheet automatically change to delimite text to columns when it opens? Thanks all, Vat -- Message posted from http://www.ExcelForum.com Vato If I understand you correctly, simply record yourself doing it once and then re-run the code in future -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Vato Loco >" <<Vato.Loco.182h63@excelforum-nospam.com> wrote in message news:Vato.Loco.182h63@excelforum-nospam.com... > Is there a way to have a worksheet automatically change to delimited &...

multipule columns as on column
I need to create a make-table query containing 6 fields.. I have 16 fields in the original table. The first is a primary key, the next fifteen and actually three groups of five (a,b,c,d,e,a1,b1,c1,d1,e1,a2,b2,c2, d2,e2) the need to be output as a,a1,a2 into a field called last name (not concatenated), b,b1,b2 into a field called first name...etc... Does anyone have any ideas On Wed, 06 Jun 2007 16:07:31 GMT, "gz3d2h" <u34843@uwe> wrote: >I need to create a make-table query containing 6 fields.. > >I have 16 fields in the original table. The first is a primary ke...

Importing email address from column in excel sheet
Wondering how to import about 60 email addresses I have in a column in an excel spreadsheet into my address book in outlook. Or even to send one email, then navigate to the email address list without actually adding them in my address book. ...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

Pivot Table data values
I need make a pivot table using the values as the data. I used to be able to do this, but now it only sums, counts or calculates. Is there a way to return the data values? ...

can I put a stacked column next to a solid column
I am trying to graph some information and can't figure out how to place a solid column that references one number next to a stacked column that references two numbers. Maybe a better question is can I put two stacked columns righ next to eachother? Hi, Have a look at the links on Jon Peltier's page. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Cheers Andy RobXCSP wrote: > I am trying to graph some information and can't figure out how to place a > solid column that references one number next to a stacked column that > references two numbers. Maybe a...

Group DB columns to be used in pivot table
Hi, I have the following problems to prepare the DB. I need to create a Pivot Table and my source DB is Excel. Headings go from Column A to AT, but only columns A to N will be used to play around with the pivot, while columns O to AT are only to input values. How can I: 1) Transform the many columns O to AT in one field called Product so that once placed into the Column part of the Pivot I can see all the headings from O to AT 2) How to make all the O to AT cells active so that the user can enter values and see the changes or analyse using the A to N fields? Examle: A B C D E F G H I...

Count Unique text in 3 different columns
Hi, I saw a few formulas that adds Unique records when is numbers, but I have a spreasheet which I need to count the Unique records (Names) taken into consideration three diffrent columns, example; Column A Column B Column C DC&D aagis Ternure DC&D aagis Ternure DC&D adamk New Hire DC&D adamk New Hire DC&D adasilva New Hire DC&D adasilva New Hire DC&D vdafons Ternure DC&D AMPATA Ternure DC&D AMPATA Ternure DC&D AMPATA Ternure DC&D AMPATA Ternure AIO Ashaw New Hire AIO Ashaw New Hire AIO Ashaw New Hire AIO Ashaw New Hire AIO A...

how do i change column labels back to letters? currently columns.
when i opened excel this evening, i found that columns and rows are both labeled with numbers. How do I relabel the columns alphabetically so that I can use formulas? thanks Click Tools > Options > General tab Uncheck "R1C1 reference style" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "slthompson" <slthompson@discussions.microsoft.com> wrote in message news:2DF283F7-A345-46E9-AD0C-148B8BDC200B@microsoft.com... > when i opened excel this evening, i found that columns and rows are both &g...

Exporting Mail Containers
I am running Exchange 5.5 and am trying to export a particular container so I can import it back into a personal address book for Outlook (standalone PC). I run an export and all my info (address, phone number, etc.) is not being imported. I created a .csv template with just the fields I wanted to come over and tried to import into it and none of it came over. Does anyone have any experience with this? Thanks, in advance. Bart ...

Assign value to variable
Several people have helped me get to the stage where I've got the following formulae working: Range("B6").Formula = "=max(h1:h300)" Range("B7").Formula = "=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))" Range("B8").Formula = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))" next question is for my 3 formulae, how do i set a variable to equal each of those values for writing to a sequential text file? TIA, Rob -- rroach ------------------------------------------------------------------------ rroach's Profile...

display total in stacked column chart
Using Windows XP and Office 2007 To make it simple to explain, I'll make up a sample stacked column chart and maybe someone can help me. Let's assume I have taken attendance in a particular class over the last 5 years and I have that attendance information broken down into males and females. So I have a data table that has the year in Column A, the number of males in attendance in Column B, the number of females in attendance in Column C, and the total attendance in Column D. I created a stacked column chart with the information from Columns B and C so that my stacked column...

Memory: negative value?
Why I can see a negative values as "In use" of "Storage memory" from Control Panel/System Properties/Memory? Max I saw this behaviour in the past on a device which used the flash disk as the root of the file system, thus it didn't have any RAM used for storage memory, is it your case? -- Luca Calligaris (MVP-Windows Embedded) l.calligaris.nospam@eurotech.it.nospam www.eurotech.it "QuantumUniverses" <quantumuniverses@gmail.com> ha scritto nel messaggio news:e%23CpfbKqKHA.556@TK2MSFTNGP02.phx.gbl... > Why I can see a negativ...

Multi-value field filter question using strFilter
Currently I have a form that has some list boxes. One of the list boxes is a multi-value multi-select and i'm having a bit of trouble getting it to work with my strfilter. Currently my code looks like this: ------------------------------- Private Sub Command28_Click() If Me.lstpayment.ItemsSelected.Count = 1 Then strFilter = strFilter & "[payment_type] = '" & Me!lstpayment & "' And " End If If Me.lststore.ItemsSelected.Count = 1 Then strFilter = strFilter & "[store_name] = '" & Me!lststore & "'...

hide a column
hi can u help me with this what i want is that if in a column there is written "Blank" a comman that hides that particular column plz help me with thi -- Message posted from http://www.ExcelForum.com Hi try the following macro (looks in row 1 for this word 'Blank') Sub hide_columns() Dim ColNdx As Long application.screenupdating=False For ColNdx = 1 to 256 If Cells(1, ColNdx).Value = "Blank" Then Columns(ColNdx).hidden = True End If Next ColNdx application.screenupdating=True End Sub -- Regards Frank Kabel Frankfurt, Germany >...

Separating data in a column
the spreadsheet i am working on has 1 column with 2 possible answers, i.e. yes/no. what i need to do is separate the replies and then total the yes replies in another cell and also total no replies in a different cell. How do i do it? The following COUNTIF function can be used =COUNTIF($A$1:$A$21,"yes") The sort or data filter command can be used to separate the replies. "Gemgirl" wrote: > the spreadsheet i am working on has 1 column with 2 possible answers, i.e. > yes/no. what i need to do is separate the replies and then total the yes > replie...

Xcel or Access sort columns of names so that I remove the intersect between 2 columns from one of the columns?
Hi, I have currently in Xcel and long list of X English phrases in one column with associated numerical data about each name in adjoining columns. I have on another workbook (which could of course be pasted into this workbook)another similarly set out column of Y English phrases again with ajoining columns having numberical data row that the phrase. I would like to be able to sort the second column of phrases removing all the phrases that occured in the first column. Ideally it would be great to have the numerical data for the remaining English phrases still on the same rows (not necessary ...

Moving a column
How do i move a Vertical column over to the right & have the information move with it ? -- Dell Inspiron Pentium dual-core 2.2 GHz 2 GB DDR2 SDRAM Windows Vista Home Premium SP1 One way: select the column header (usually a letter at the top) Rightclick on that selection And select insert desgnr wrote: > > How do i move a Vertical column over to the right & have the information > move with it ? > > -- > Dell Inspiron > Pentium dual-core 2.2 GHz > 2 GB DDR2 SDRAM > Windows Vista Home Premium SP1 -- Dave Peterson Clic...

HELP! finding highest values within data
hi couple of things: is there anyway that excel can atomatically detect the highest value within a data set, and then insert this value into another cell? eg. 213 245 267 211 497 is there anyway to make excel select the 497 and then copy this to another cell? also, after identifying this as the highest value, would it be possible to then automatically select the cell immmediately to the left of "497" in this example? thanks, nick x ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from htt...

Copying only the numerical values
Is it possible to have a summary worksheet in an Excel spreadsheet and then copy this worksheet on its own to another document but with the numerical values only? I have a worksheet which is a summary of various other Excel documents linked together. I wish to email this summary page with the numerical values only. I don't particularly want the file paths showing in the document I email - I just wish the numerical values - not the formulas which produce these values. Is this possible? Without going through complicated procedures. "SM" <theesel1994REMOVE@googlemail...