SUM function in VB module

[fourgt]

I am sure this has been asked and that I am just not finding the right solution in my search, but I am trying to get a simple formula moved to a VB module instead of staying in the formula bar.

I am adding the sum of multiple cells in a row and having the result post into the J column. For example in cell J3 I have
=N3+R3+V3+Z3+AD3+AH3+AL3+AP3+AT3+AX3+BB3+BF3+BJ3+BN3+BR3+BV3+BZ3+CD3+CH3+CL3+CP3+CT3+CX3+DB3+DF3+DJ3+DN3+DR3+DV3+DZ3+ED3+EH3+EL3+EP3+ET3+EX3+FB3+FF3+FJ3+FN3+FR3

J4 is =N4+R4+V4+Z4..............etc

This continues down the entire J column.

Instead of having the formula sitting there in each J cell, I would like to move it to a VB module to run if possible.

Secondly I have a (now)date\time stamp VB module in another column, but could not get it to run on J results because of the formula, so my script looks like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("N:N,R:R,V:V,Z:Z,AD:AD,AH:AH,AL:AL,AP:AP,AT:AT,AX:AX,BB:BB,BF:BF,BJ:BJ,BN:BN,BR:BR,BV:BV,BZ:BZ,CD:CD,CH:CH,CL:CL,CP:CP,CT:CT,CX:CX,DB:DB,DF:DF,DJ:DJ,DN:DN,DR:DR,DV:DV,DZ:DZ,ED:ED,EH:EH,EL:EL,EP:EP,ET:ET,EX:EX,FB:FB,FF:FF,FJ:FJ,FN:FN,FR:FR")) Is Nothing Then
Range("I" & Target.Row).Value = Now
End If
End Sub

If I change the 1st formula to run via a VB module instead of a formula, can I edit the 2nd module to just look in J column for changes?

Any assistance would be greatly appreciated, hopefully I explained this correctly.

Matt

 

[JLGWhiz]

I had to jerry-rig the Union function to do it, but this code should work. It was not clear if you wanted it as event code or a regular macro. This is a regular macro, but it sums all of the rows and time stamps each one in column I. If you want it as event code then you need to clarify if you want all rows summed and time stamped each time a single change is made, or if you want to sum and stamp only the changed row.

Sub addMup()
Dim sh As Worksheet, lr As Long, rng1 As Range, rng2 As Range, Trng As Range, i As Long
Set sh = ActiveSheet
lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
For i = 3 To lr
    Set rng1 = Union(Range("N" & i), Range("R" & i), Range("V" & i), Range("Z" & i), Range("AD" & i), Range("AH" & i), Range("AL" & i), _
    Range("AP" & i), Range("AT" & i), Range("AX" & i), Range("BB" & i), Range("BF" & i), Range("BJ" & i), Range("BN" & i), Range("BR" & i), _
    Range("BV" & i), Range("BZ" & i), Range("CD" & i), Range("CH" & i), Range("CL" & i), Range("CP" & i), Range("CT" & i), Range("CX" & i))
    Set rng2 = Union(Range("DB" & i), Range("DF" & i), Range("DJ" & i), Range("DN" & i), Range("DR" & i), Range("DV" & i), Range("DZ" & i), _
    Range("ED" & i), Range("EH" & i), Range("EL" & i), Range("EP" & i), Range("ET" & i), Range("EX" & i), Range("FB" & i), Range("FF" & i), _
    Range("FJ" & i), Range("FN" & i), Range("FR" & i))
    Set Trng = Union(rng1, rng2)
    sh.Range("J" & i) = Application.Sum(Trng)
    sh.Range("I" & i) = Now
Next
End Sub

Your current event code will run only if changes are made in cells other than column J.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I was a bit confused about your requirements as well. My first attempt calculated column J for every row using an array for speed. However, when I re-read the question, I thought you might be trying to form the sum for one row only and then datestamp it each time a contributing cell changed.

In which case, this event macro might be what you are looking for. You need to install it as a worksheet macro for the sheet with the data.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Target.Row <= Cells(Rows.Count, "N").End(xlUp).Row And _
        Target.Row >= 3 And _
        ((Target.Column - 14) Mod 4) = 0 Then

        ' Clear the row total
        rowSum = 0
        
        ' Loop along the row to sum it
        For j = Range("N:N").Column To Range("FR:FR").Column Step 4
            rowSum = rowSum + Cells(Target.Row, j)
        Next
        
        ' Write out the result
        Cells(Target.Row, "J") = rowSum
        Cells(Target.Row, "I") = Now
        
    End If

    Application.EnableEvents = True

End Sub

I noticed that all the columns you check are 4 columns apart so was able to check them using the Mod function.
When a change is made in one of those columns the macro calculates a sum for that row, writes the sum in column J and datestamps it.

 

[fourgt]

JLGWhiz / Rick - Thank you for your assistance. The script works great as a macro. Yes I should have been more clear as to what I was looking for, ultimately I was hoping as values change in:
(N3+R3+V3+Z3+AD3+AH3+AL3+AP3+AT3+AX3+BB3+BF3+BJ3+BN3+BR3+BV3+BZ3+CD3+CH3+CL3+CP3+CT3+CX3+DB3+DF3+DJ3+DN3+DR3+DV3+DZ3+ED3+EH3+EL3+EP3+ET3+EX3+FB3+FF3+FJ3+FN3+FR3)

Rick - you are right its every 4th cell.

The total sum of the above cells would be put in column J and when any of the above cells changed the (now) date time would be put in column I, both automatically (ideally). I tried to get the VB (now) to change just if J changes, but since it’s a formula in there now it would not work, so I created it based on all the cells listed above.

Currently it works with a formula in J and vb script for I.

Hope that is clearer, as I would like J & I to change as users change the other cells listed above (every 4th cell).

Matt

 

[fourgt]

Hi and welcome to the MrExcel Message Board.

I was a bit confused about your requirements as well. My first attempt calculated column J for every row using an array for speed. However, when I re-read the question, I thought you might be trying to form the sum for one row only and then datestamp it each time a contributing cell changed.

In which case, this event macro might be what you are looking for. You need to install it as a worksheet macro for the sheet with the data.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Target.Row <= Cells(Rows.Count, "N").End(xlUp).Row And _
        Target.Row >= 3 And _
        ((Target.Column - 14) Mod 4) = 0 Then

        ' Clear the row total
        rowSum = 0
        
        ' Loop along the row to sum it
        For j = Range("N:N").Column To Range("FR:FR").Column Step 4
            rowSum = rowSum + Cells(Target.Row, j)
        Next
        
        ' Write out the result
        Cells(Target.Row, "J") = rowSum
        Cells(Target.Row, "I") = Now
        
    End If

    Application.EnableEvents = True

End Sub

I noticed that all the columns you check are 4 columns apart so was able to check them using the Mod function.
When a change is made in one of those columns the macro calculates a sum for that row, writes the sum in column J and datestamps it.

Rick - I left the office, I will try this tonight when I get home a report back, thanks.

 

[fourgt]

Hi and welcome to the MrExcel Message Board.

I was a bit confused about your requirements as well. My first attempt calculated column J for every row using an array for speed. However, when I re-read the question, I thought you might be trying to form the sum for one row only and then datestamp it each time a contributing cell changed.

In which case, this event macro might be what you are looking for. You need to install it as a worksheet macro for the sheet with the data.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Target.Row <= Cells(Rows.Count, "N").End(xlUp).Row And _
        Target.Row >= 3 And _
        ((Target.Column - 14) Mod 4) = 0 Then

        ' Clear the row total
        rowSum = 0
        
        ' Loop along the row to sum it
        For j = Range("N:N").Column To Range("FR:FR").Column Step 4
            rowSum = rowSum + Cells(Target.Row, j)
        Next
        
        ' Write out the result
        Cells(Target.Row, "J") = rowSum
        Cells(Target.Row, "I") = Now
        
    End If

    Application.EnableEvents = True

End Sub

I noticed that all the columns you check are 4 columns apart so was able to check them using the Mod function.
When a change is made in one of those columns the macro calculates a sum for that row, writes the sum in column J and datestamps it.

Rick - Thank you a million times over, this works perfectly and so easy to follow and I can make change to the code if needed. I appreciate it.

JLGWhix- Thank you, the macro also works perfect and will come in handy for some other things I am thinking about.

Thanks again to all, I hope this also helps someone else looking to do the same.

Matt

 

[JLGWhiz]

Rick - Thank you a million times over, this works perfectly and so easy to follow and I can make change to the code if needed. I appreciate it.

JLGWhix- Thank you, the macro also works perfect and will come in handy for some other things I am thinking about.

Thanks again to all, I hope this also helps someone else looking to do the same.

Matt

Happy to assist,
Regards, JLG

 

[RickXL]

No problem.

I'm glad you liked it.

Regards,