Find Hard Coded Numbers in Certain Worksheets between a range of rows

I have a workbook with over a hundred tabs. In some cases I have to manually adjust things, and that would be hardcoding a number to circumvent a formula. when we roll the file over I have to go back and eliminate and clear out these hard coded numbers and put the formula back in.

I would kindly like to request a code that would search worksheets that contain a underscore. The workseets that would contain these hard coded numbers would be ones named in this fashion 101021_Smith.

Where I would put the hard coded number would be in these worksheets, in the standard mentioned above with the underscore and it would be between lines 11 and 60.

would it be possible to create a code to look in these worksheets between these lines and create a sheet that would list where it is at, meaning which worksheet and which cell so I can quickly go in and adjust the formula.

I hope makes sense
Thank you for your consideration.

I am not sure if my message went through last time I posted a response but not sure why its not showing up here.

Anyways, thank you so much for taking the time out of your day to write this beautiful code. Can I please please request 3 small changes.

1. Can the code also be adjusted to list the sheet name the location and the value?
eg. Sheet_1 $E$13 55
2. Can the sheet called Cells with Fixed Values be cleared first before the code is ran? As I work through to fix all of the numbers and apply a formula I would like to re run this and be refreshed with the most current information.
3. Can I also request the sheet name to be on every single row.

Thank you thank you again.
And I hope that I am not too much of a burden to you

Wow, thank you so much! Thank you thank you thank you....

This will save me a lot of headaches.

Is it too late to add one more request. Last one I promise, can the code ignore 0 values?

Thank you.

I am getting an error message saying Compile error: Sub or Function net defined it stops at the " SkipC point. See last line below. Maybe its the version of excel I have?

Please Login or Register  to view this content.

The code is missing a colon (shown in red) on the last line of the snippet you posted...

Please Login or Register  to view this content.

Thank you all. you are all great help.

I just got a chance to run this code and now I am getting an error at the Length section.

Same error. Not quite sure how to resolve it.

Please Login or Register  to view this content.

Forgive me once again. When I ran the code it is spitting out a lot of rows in this rather large file already. Is it possible to have this data to be put out to a new workbook rather then be contained with this workbook.


Ughh, sorry guys. I have more time now to really run this through and now more things are starting to come out.

I admit I am a novice but I did reading and adjusted the code slightly

from

Please Login or Register  to view this content.

It seems to be working but I am definitely not an expert.

I feel really really terrible for repeatedly bugging you but unfortunately I am not expert and this code is awesome and I do not want to cause something that makes this code not work. Believe me I want to learn how to do this but you guys have many many years of experience with this.

Sorry again.

I finally had some time to test the code file in the master document. When I export a few sheets out, and run the code to test, it seems to work fine but in my master filet that contains over 170 sheets it seems to give me results that do not exist. And I do not quite understand why, I do get a runtime error report at the end but that i think is an error from not finding anything in a blank space. The file also seems to be taxed after running the code and behaves rather slow.


It repeatedly displays that every sheet contains 1 and .77 in cells M44 and H44. In M44 this is the formula that is in there =IF(M25=0,0,$E$17). In H44 =IF(H25=0,0,$E$17).

Workseet $M$44 1
Worksheet $M$44 1
Worksheet $H$44 0.77


I am not sure if its the amount of worsheets it is going through? It just doesn't make sense. Any ideas why this would display this?

Can't figure it out.

I know, when I just take a few of my sheets out it works perfectly but when I include it in my master document those values come back on almost every sheet. I really cannot explain it. I am not sure if its because it gets overwhelmed somehow? The other thing I noticed it once the code stops running it puts my calculation to manual mode. I wish I could share my workbook but there are so many proprietary things in there..I would have to remove alot of things out of there which would defeat the purpose. Last ditch effort, is it possible for the code to only look at
worksheets with this criteria?

Please Login or Register  to view this content.

If this doesn't work then I am still much appreciative for your help and your time. I will continue to play with this code to isolate the issue. i know when dealing with code it could be a million things and it is difficult for you to really understand what is going on, as every computer and spreadsheet is set up differently. I get it.

Have a wonderful day.

Oh wow, I think you fixed the issue.

If I only want to have the code to look between e25:p70. . Because every sheet in column T has "1" which I set certain lines to so I can filter easier. I want the code to ignore that since it is not what I want to look at. If I look in that range then I avoid the extra lines which I do not want to look at anyways. Sorry for this.

I tried using Range ("e25:p70") but it gave me the same crazy results as described above. I must have to use a different code snippet for this to work?

Thank you again.

Thank you for putting up with me...At this point I think we can can give up, I just can't get it to work. It must be something inside my sheet. I set the range to E44:P44 and I am still getting results that do not exist. Here is an example, none of these values exist in those cells. For example H44 contains the following formula. =IF(H25=0,0,$E$14) and E44 contains =IF(E25=0,0,$E$14). The other thing is it returned line 46 while the range is 44 in the code. Is it offsetting somehow? I do have a filter in column T that filters out lines so that when we distribute these the employee does not see all the calculations we make in their sheet. Like I said I don't blame you if you want to give up. I have hit you with things daily and I am sure it is getting annoying now. Thanks for all your work.

$H$44 0.77
$E$44 0.38
$G$44 0.8
$I$46 1
$L$44 0.8
$M$44 0.8

Please Login or Register  to view this content.

I put one of my worksheets in there and it did the same thing. I reported the output of the file included in the attached file.
Of course I had to delete most of the contents otherwise I would be out of this job quicker than you can say Jamiroquai.

Thank you for your continued willingness to help.

Did I lose your motivation??

No worries, I just wanted to see if you are still interested in helping. If not then I was just going to close the thread. But thank you for acknowledging.

Can anyone take a look at this code and help if possible....please and thank you.