CODEWITHSUNDEEP
excelvba
findwindow
findwindow

Reputation: 3153

Need logic for checking range in formula

Need help with logic. I have about 1000 rows of data where formulas were entered to calculate subtotals and totals. The nature of the formulas require each respective range to be correct. The correct range is determined by data in column C (please see illustration). For the most part, the ranges (and in turn the formula) are correct. But I have reason to believe a handful are not.

I can check them all manually or I can ask SO for help ^_^ My current thought is to loop column A and at every Subtotal, dump the rows counted so far into column V and pull the rows in the formula from column G into column W then compare V and W.

But what do I do when I reach a Total? My current thought would become cumbersome. Is there a better way? All the highlighted 0s between columns i:u need to be tested as well.

I will entertain solution to replace all formulas from scratch as well.

enter image description here

Upvotes: 2

Views: 75

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

I'm not sure if this is what you are looking for, but I wanted to run it by you as an idea. This example will put the totals from column G into Column Z.

Anyway, check it out, if you don't like it, I'll get rid of it. You can add another line or make up a loop to get all the columns total.

Sub Button1_Click()
    Dim RangeArea As Range

    For Each RangeArea In Columns("C").SpecialCells(xlCellTypeConstants, 1).Areas

        Cells(Rows.Count, "Z").End(xlUp).Offset(1).Value = Application.Sum(RangeArea.Offset(, 4))

    Next RangeArea

End Sub

Upvotes: 1

Related Questions