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
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