Converting formulas in a Range of CELLS which are in between SUB-TOTALs to Values with VBA

Converting formulas in a Range of CELLS which are in between SUB-TOTALs to Values with VBA

Dear Forum,

As I am at work cannot upload a file due to the strict policy however what I require is to Convert My Range of Specific Cells into Values however I need to only make specific cells which have no fill color (only these cells which have a formula into values ) as the Entire Range would have two different sets of formulas
Ex - Formula 1 = "=SUMIFS('DATA (New)'!$N:$N,'DATA (New)'!$R:$R,INDIRECT(ADDRESS(7,COLUMN(),2)),'DATA (New)'!$D:$D,CHOOSE(MATCH(INDIRECT(ADDRESS(4,COLUMN(),2)),{""HSPL"",""BANK"",""TPDSA"",""CCST"",""DIGI"",""DIRECT""},0),""HLSIL"",""HDFC BANK LTD"",""TPDSA"",""CCST"",""DIGI"",""WALKIN""),'DATA (New)'!$E:$E,INDIRECT(ADDRESS(ROW(),3,3)))"

Formula - 2 = SUM(A2:A5) as these are Sub-Total Cells of the above rows 2,3,4 & 5 LIKEWISE...

Please Login or Register  to view this content.

This code works only when the Data Is Filtered , i need this to work even when the Data is not Filtered so how do I achieve the same result...

Regards
e4excel

Dear Forum,

Can something be tweaked to only work only on the cells in that Range where the Fill Color is No Fill as the In between Sub-Total Cells have a different Fill Color..

Regards
e4excel

These cells have formula and the cells filled with color also contain a different formula, I just need the formula to work on the cells which are NO FILL and without applying the filter..

Dear Rorya,

In that Range from ROw 8 till 55 there are 2 different sets of formulas - one is SUMIF from ROW 8 till 12 then in the cell wih ROW 13 will contain the Summation of the cells from 8 till 12, so I just need to convert the cells with the SUMIFS formula into value and the cells which act as SUB-TOTALs to be kept as it is (i.e. they should have formulas )..

Originally Posted by rorya

If they originally have formulas then you can loop through them using

Please Login or Register  to view this content.

but you will need to check the fill for each one before processing it - eg test if the cell's Interior.Colorindex property equals xlcolorindexnone

The above code changes all the formulas into values and does not distinguish between the Fill Color.

No the SUM formula has Dark Grey Color as that is the ROW which acts as SUBTOTAL Row and the SUMIFS in between need to be converted into values so my code is working fine however I have to always filter the data in order to use it, i need something which can differentiate without filetering the data...

Originally Posted by rorya

Then I suggest you look at what I posted in post 5. Although if all of the cells in that range are formulas, there is no point to specialcells - just loop through all the cells.

Please Login or Register  to view this content.

This does not differentiate between the 2 Sets of Formulas...so how do I make it work only on the cells which are not filled with any color.

Originally Posted by rorya

As I said before:

Ok I got it now, this worked..

Please Login or Register  to view this content.

Originally Posted by rorya

You commented out the wrong line. Your version is - purely by coincidence since xlfilternofill happens to have the same value as xlcelltypevisible - looping through all the visible cells in the range

Please Login or Register  to view this content.

Dear Rorya,

Plesae advise...