trying to create button to look at cells if "Alert" is in cell then it copies the row

[Dadof2]

info for that category over to Sheet1

I am trying to look at a bunch of bonds and have my own alerts that warn me of potential problems.

The info gets to intensive as there are over 1000 line items (I grabbed just a few to get the concept down)

I would like to see Sheet1 have bonds which are alerted positioned under that Row 1 (starting at cell E1 to L1) pt over on Sheet1 in an easy way to just see that alerts.

Does this make sense?

Thanks for looking
Mark

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[Dadof2]

John - Thanks for helping.

When I run the code in the first sheet i attached it works great. However, when I run that same code in my main sheet it includes to much data on the Sheet1 tab (I attached it). I can't figure out why it works on one file and not the other??

Thanks!
Mark

[JOHN H. DAVIS]

Is this any better?

Please Login or Register  to view this content.

[Dadof2]

The first method works better but I am still getting. see attachment to see all the extra info (without alerts) and how it cuts the data off and just stops around line 66 or so

Thanks
Mark

[JOHN H. DAVIS]

Can you provide both sheets in your sample. (ie sheet to copy from, Sheet with results).

[Dadof2]

I see where the problem is created.

when I copy/paste the values into a new book (without the formulas), to attach here for you, everything works great in that new sheet. it works perfectly

the problem is when I apply the code to my main book with all the formulas and I click the button I get too much data. I think the formulas within each cell in columns E-L are causing the problem since Sheet1 is basically a duplicate of Sheet2.

If there a way to make the code ignore any formulas and just look for "Alert"?? Does this make sense?

thx

[JOHN H. DAVIS]

Give this a try:

Please Login or Register  to view this content.

[Dadof2]

I think that got it! I will test it a bit. Thanks!!

[JOHN H. DAVIS]

You're welcome. Glad to help out. Hope it works for you.

[Dadof2]

John - last question. How do I get the code to clear the Sheet1 before it runs. I want to see a fresh sheet each time. I also have a title row (Row 1) on Sheet1 so can it clear everything below that first then run?

thx
Mark

[JOHN H. DAVIS]

Maybe try:

Please Login or Register  to view this content.

[Dadof2]

That works. Thanks

For future reference, if I want to search for more than one thing (currently just "Alert") do I add 'Or' in the code

for example, I want to see cells equal to "Alert" and "Fix".

Do I just do this:

If Cells(i, y).Value = "Alert" Or "Fix" Then

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[Dadof2]

very nice! Thanks again

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as SOLVED. See link for guidance.

http://www.excelforum.com/forum-rule...rum-rules.html