how do I find a value in all the excel 2010 worksheets ?

I have an excel file that has mutiple worksheets.   I have a summary worksheet call "List".  On this worksheet I list all the names of the other worksheets.  Now I want to look for a text "PJMNT" in all the other worksheets and enter that in "List" worksheet in column "B" next the names.  Here is my code so far but cannot seem to use the find feature.  Thank.  Nick.

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim x As Integer
Dim foundcell As Object

x = 1

For Each ws In Worksheets

     Sheets("List").Cells(x, 1) = ws.Name

     'Sheets("List").Cells(x, 2) = ws.Cells.Find("PJMNT")  NOT WORKING - NEED help 

     x = x + 1
Next ws
End Sub

Open in new window

countyprobSystem AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What are you wanting to show in cells(x,2)?  Is there only one cell on each tab that contains this string?  Do you want it to show the location of the string on that page or do you want it to show the full string?
countyprobSystem AnalystAuthor Commented:
Each of the sheets contain a string called "PJMNT", "PJMNT2", and "PJMN3".  I would like the "List" worksheet to display the names of the sheets and then next the next column to indicate if those other sheets have the strings.  for example

column A             column B    Column C         Column D
sheet names      PJMNT         PJMNT2           PJMNT3

sheet1                x                                       x
sheet2                                   x                    x
sheet3                x                                       x
sheet20                                 x  

I want to find the matching string on each worksheet (sheet1, sheet2 etc...) and then put it on my "List" sheet next to the sheet names.
If you use PJMNT (and not PJMNT1), then it's going to show a match, even if the cell contains PJMNT3.  Does the entire cell's contents contain "PJMNT" or is it part of a much larger string.  I'm trying to determine how to match the data.


Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

countyprobSystem AnalystAuthor Commented:
Yes, the entire cell content will contain the string.  It is NOT part of a much larger string.  A worksheet could contain all three strings on seperate cells.  Thanks. nick.
See if this works for you.  Let me know if it doesn't or if you need to do it another way.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
countyprobSystem AnalystAuthor Commented:
thanks a bunch. it worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.