SUM variable range LOOP

[elnicca]

Hi guys,

So this is my first post though I have been reading you for a while.
I wanted to share with you a code I created a while ago.
It works well but there is one thing pending to make it perfect. Maybe you can help me achieve this.

What this does is basically insert a SUM (below selected cell) and sets the range with the consecutive rows above the cell selected. Then (below sum function) it checks the difference between the selected cell and the new calculated SUM.


What I need help with is adding a loop function so if the sum() does not equal the selected cell then the range gets extended one cell up untill that value is reached or top of the page is reached.

The code I have today:

Sub AutoFootv2()
'
' AutoFootv2 Macro
' redo SUM, Compares against selected cell
' pending: extend sum range if selected cell and redone SUM are not equal
'
Dim rngStart As Range
Dim rng As Range
Set rngStart = ActiveCell
Set rng = Range(ActiveCell.Offset(-1, 0), ActiveCell.End(xlUp))
If ActiveCell.Offset(1, -1).Value = "" And ActiveCell.Offset(2, -1).Value = "" _
Then
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
rngStart.Select
ActiveCell.Offset(1, 0).Formula = "=sum(" & rng.Address(False, False) & ")"
ActiveCell.Offset(2, 0).Formula = "=R[-2]C-R[-1]C"
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Select

Else
ActiveCell.Offset(1, 0).Formula = "=sum(" & rng.Address(False, False) & ")"
ActiveCell.Offset(2, 0).Formula = "=R[-2]C-R[-1]C"
ActiveCell.Offset(1, 0).Select
End If
End Sub

Visual Example: Here I would need the macro to continue extending range up untill C3 is reached and the calculated sum equals the initial selected cell C7
10221

Anyway hope its clear and maybe you guys can give me a hand on this one.


Thanks!


Nick

[patel]

attach please a sample file (not image) with current status and desired result

[elnicca]

Attached is the Example File.

I put comments, an example of the macro already run and another example for you to run the macro and see where the problem is.

Hope this is clear enough. I appreciate the help!


10226


Nick

[snb]

And then ??


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
For j = 1 To Target.Row - 1
If Application.Sum(Target.Offset(-j).Resize(j)) >= Target.Value Then Exit For
Next
Target.Offset(-j).Resize(j).Select
End Sub

[elnicca]

And then ??


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
For j = 1 To Target.Row - 1
If Application.Sum(Target.Offset(-j).Resize(j)) >= Target.Value Then Exit For
Next
Target.Offset(-j).Resize(j).Select
End Sub




Im sorry I dont think I understand... you are suggesting I take that as a model to apply on my macro?

[snb]

It's meant to replace your macro because it performs what you were asking for. (did you test it ?)

[elnicca]

I actually did try. As you can figure, I am no expert with this, I just record macros and then edit them and complement with a book I downloaded. When I tried running that by itself Excel pulls up the macro window and asks me to pick a macro of the ones I already have so it doesnt work. I also tried adding it to the current code I shared with you but couldnt manage to do it well


I was looking at your code being a Private Sub, Worksheet Event and, considering that, I want to explain something else, I didnt explain before since I didnt think it would make a difference:

I use this macro for work, so what I´ve done is Saving it as an Add-In file so it gets loaded on background with any file I need to work on.
Of course when I have that range issue I correct it myself. Thats why i was suggesting a Loop function to complement the actuall macro.


Thank you,

Nick

[Aussiebear]

There's a difference between those you record and those you write/construct. You can tell the difference because of the word "Private" at the start of the section of code.

The one snb has provided you with should be put in a Sheet module of the sheet you wish it to refer to.

[elnicca]

Ok now I understand, sorry about that, (I will investigate private subs to see if it can help me in any othe way!)

So, I used the code in the same example file I uploaded here and when I double click on cell F7 (10) then excel highlights the cells that sum that number. Thats great!
However, the thing is I need excel to do that to the SUM function, so SUM(F4:F6) should be updated untill it reaches F7 value so it would be SUM(F2:F6). Does that make sense?

Keep in mind that I use this macro as an add-in so I have it available on all the company balance sheets I have to verify, so I dont have to put the code on each single sheet I need to check on (some excel files I work on have over 50 sheets)

Any way to get it to do that?

Thanks!! Nick

[snb]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
For j = 1 To Target.Row - 1
If Application.Sum(Target.Offset(-j).Resize(j)) >= Target.Value Then Exit For
Next
Target.Offset(1) = "=sum(" & Target.Offset(-j).Resize(j).Address & ")"
End Sub


@Aussiebear

Thank you for your helpful explanation :)