Solved: Add a 'sum' function in the 'worksheet_change' Procedure.

[artds]

Hi guys! I have this minor problem.:(

I have a data entry sheet for the user to key in their sales volume for the quarter by weeks, i.e. 13 weeks.

Thus its from Column 'A' : 'S'.
Column 'A' is for product, 'B' for Name, 'C' for ID, 'D' for Quarter, 'E' for type
From column 'F':'S' is for Week 0 to Week 13.
Column 'A' to 'E' is a mandatory fields which the user must fill in, via Data validation - drop down box.

So the problem is that, I want to automatically insert the sum formula('=sum(F:S)') in column 'T' via VBA, once column 'A' to 'E' for that particular row is keyed in. So for example in row 10, after the user finish entering values from Column 'A' to 'E', the formula('=sum(F10:S10)') will be inserted into column 'T'.

I know that in the 'worksheet_Change' procedure i must use this syntax,


Set rChange = Intersect(Target, Range("A:A"))
For Each rCell In rChange
If rCell > "" Then
rCell.Offset(0, 1).formula = "=sum(F:S)"
end if
next

As you can see, thats where the codes lost me. Can you guys help out? Thanks in advance.



regards
artds

[herzberg]

You can try this out:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CheckRange As Range
Set CheckRange = Range("A:E")

If Not Intersect(Target, CheckRange) Is Nothing Then
Cells(ActiveCell.Row, "T").FormulaR1C1 = "=SUM(RC[-14]:RC[-1])"
End If

End Sub
Actually, I think it may be easier (and faster) to update the totals all at once after all the data is entered, rather than updating a row one at a time, since the check would not be triggered every time there's a change in worksheet values.

[artds]

Hey herzberg (http://vbaexpress.com/forum/member.php?u=12575), the codes work like magic! Thanks!!

As to answer your question, is simple to prevent user faults. I'm trying to make it as easy as possible for the user to use it. As both the total and the weekly data will be used in a calculation on a separate sheet. In the end if theres any problem with the calculation, its my head that will be on the chopping board. As It won't be me using in. I just provide the tools.


anyways, obrigado(thank you in portugese........... I think)
cheers
artds

[Bob Phillips]

I would just do it on column A, so as not to do it 5 times per row, and also check for blank in A, then remove it.

[artds]

I would just do it on column A, so as not to do it 5 times per row, and also check for blank in A, then remove it.
do you mean put the CheckRange set it "A:A"? I did thought of that, and if its only column 'A' then you can add this line, so as to make the VBA more efficient.



If Intersect(Target, CheckRange) Is Nothing Then
Cells(ActiveCell.Row, "T").ClearContents
Else
If Not Intersect(Target, CheckRange) Is Nothing Then
Cells(ActiveCell.Row, "T").FormulaR1C1 = "=SUM(RC[-14]:RC[-1])"
End If
End If



I need to look at this again and will get back to you xld. As I have mentioned before Column A:E are mandatory. It must not be empty if an accurate calculations is neccessary. So in a way, i'm trying to implement a sort of 'error checking' whereas if the 5 coloumn is empty instead of msgbox, the sum formula will not show.

Thanks for you inputs xld. very much appreciated.

regards
artds

[Bob Phillips]

Yes I do mean just A:A, but clearing it like that means that any other change removes the formula, surely not what you want.

I would use



Private Sub Worksheet_Change(ByVal Target As Range)
Dim CheckRange As Range

On Error GoTo ws_exit
Application.EnableEvents = False

Set CheckRange = Range("A:A")

If Not Intersect(Target, CheckRange) Is Nothing Then
If .Value <> "" Then
Cells(Target.Row, "T").FormulaR1C1 = "=SUM(RC[-14]:RC[-1])"
Else
Cells(Target.Row, "T").Value = ""
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

[artds]

xld, come to think of it, who am I to argue with a VBAX Mentor?:bow:
heehe. So i'm gonna use ur suggestion. Thank you for all your time and advice.

One more question xld. I'm currently working on an excel project. Once its about 90% finish, will it be ok if I e-mail it to u for review? But it won't be that soon, perhaps in another months time. I'm not asking you to recode it or anything, just comments on my overall project and advice to help me improve on VBA. How abt it?


herzberg (http://vbaexpress.com/forum/member.php?u=12575), I really appreciate your help and efforts.:thumb thank you


regards
artds

[Bob Phillips]

Okay, but it will have to be on an as-available basis.