VBA .Find Not Working with Formula

[thejg89]

Hello,

I'm working on a dynamic valuation model with 80+ worksheets. The 'Settings' worksheet has numerous check boxes to determine which sheets need to be visible or hidden. Within in sheet, there are rows that only need to be shown if the corresponding account detail 'sheet' is visible and a column that will alert the user if they need to 'HIDE' the row.

For example: lets assume on a Balance Sheet it lists 'vehicles and cars' as a row but if the 'vehicles and cars' detail sheet is hidden there would be an indicator on the Balance Sheet to 'HIDE' this row as it is not used in the analysis. Hopefully that helps explain the functionality.

I have the code working for 'hardcoded' cell that = "HIDE" but it doesnt work if 'HIDE' is the result of an IF Statement (continuing the example ... one the column that indicates if a row needs to be hidden would have an equation something like =if( settings sheet checkbox for 'cars/vehicles' = checked, 'blank', 'HIDE').

I've included an excel sheet, over simplified, but it should show the general understanding. The code was under a module but i moved it to ThisWorksheet. Below is also a copy of the code with comments as to what I was trying to accomplish

This is my first post - so THANK YOU!!!! everyone who tries to help

Please Login or Register  to view this content.

[KOKOSEK]

Hi

Please edit your post and use

around VBA code.


Weird...
Try:

Please Login or Register  to view this content.

[thejg89]

Thanks for the tip on the

Please Login or Register  to view this content.

- I didnt know how to get it to display properly. Unfortunately the 'lookin:xlValues' didnt work. When I use and IF statement = "HIDE" it comes up as true. I'm debating doing:

Please Login or Register  to view this content.

This works for some reason but I wanted to use the .Find and .FindNext as I'm assuming its faster. But for now I'll have to work with a double For Each with IF Statements nested in the middle

[Fluff13]

Your problem is this line

Please Login or Register  to view this content.

This means that rnCheck does not include cells with formulae.
Try

Please Login or Register  to view this content.