Problems with code in Workbook_change

[angiem]

I have a workbook, with a worksheet for each week, this has 7 days data and then combines into one for the week.


1. When I run this code it works on the daily data, but not all of the weekly. It will update some, and not others!!

2. I want to total all of the columns, but can't find what I need to do.

Any ideas

 

[SkipVought]

Hi angiem,

My car won't go! Tell me what to do.

You are going to have to be alot more specific about the structure of EACH of your sheets and exactly what is or is not happening under what circumstances for ANYONE to begin to provide cogent assistance.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[angiem]

ok basically, I have 7 blocks 1 per day all exactly the same with a block to hold the weekly totals for each category.
rp rf wi tp em total
n u n u n u n u n u n u
1 1 1 1 1 1 1 1 1 1 5 5
2 2 2 2 2 2 2 2 2 2 10 10
_________________________
3 3 3 3 3 3 3 3 3 3 15 15

the code I have updates all the daily ones no problems, but when it updates the weekly one is only updating the first four columns, I know I must be doing something stupid, and every time I have tried to get the columns to total at the bottom, I get in an endless loop. Any help would be appreciated.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)


If Left(ActiveSheet.Name, 4) = "Week" Then
If target.Row > 14 Then
Select Case target.Column
Case 16, 18, 20, 22, 24
ActiveSheet.Cells(ActiveCell.Row, 26).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 2).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 17, 19, 21, 23, 25
ActiveSheet.Cells(ActiveCell.Row, 27).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 3).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 30, 32, 34, 36, 38
ActiveSheet.Cells(ActiveCell.Row, 40).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 4).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 31, 33, 35, 37, 39
ActiveSheet.Cells(ActiveCell.Row, 41).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 5).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 44, 46, 48, 50, 52
ActiveSheet.Cells(ActiveCell.Row, 54).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 6).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 45, 47, 49, 51, 53
ActiveSheet.Cells(ActiveCell.Row, 55).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 7).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 58, 60, 62, 64, 66
ActiveSheet.Cells(ActiveCell.Row, 68).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 8).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 59, 61, 63, 65, 67
ActiveSheet.Cells(ActiveCell.Row, 69).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 9).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 72, 74, 76, 78, 80
ActiveSheet.Cells(ActiveCell.Row, 82).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 10).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 73, 75, 77, 79, 81
ActiveSheet.Cells(ActiveCell.Row, 83).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 11).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 86, 88, 90, 92, 94
ActiveSheet.Cells(ActiveCell.Row, 96).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 12).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 87, 89, 91, 93, 95
ActiveSheet.Cells(ActiveCell.Row, 97).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
ActiveSheet.Cells(ActiveCell.Row, 13).FormulaR1C1 = "=rc[14]+rc[28]+rc[42]+rc[56]+rc[70]+rc[84]+rc[98]"
Case 100, 102, 104, 106, 108
ActiveSheet.Cells(ActiveCell.Row, 110).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
Case 101, 103, 105, 107, 109
ActiveSheet.Cells(ActiveCell.Row, 111).FormulaR1C1 = "=rc[-10]+rc[-8]+rc[-6]+rc[-4]+rc[-2]"
End Select

End If

End If



End Sub

 

[VBAjedi]

angiem,

Skip is right, we need a much better description of just what you are trying to accomplish. As near as I can tell from your second post, your worksheet is laid out to track two kinds of data (n and u), each coming from two different sources (people/locations?) for 5-day periods. You want to see a daily total by data type (n and u), and a 5-day total for each data source (person/location?).

Is that correct? If not, modify and repost my description above to specify exactly what you are after. Also explain why you think this should be done via code instead of just typing your formulas into the worksheet (ex. "I get data in this format every week and I need to add the subtotals to it when I get it&quot. Then maybe we can suggest a worksheet layout that will work better, a different approach, and/or code to automate the totalling.

Once we get the right details, we can help you!


VBAjedi

 

[SkipVought]

Well one bit a quick advice...

Don't run your code directly in an event. Call a procedure that is in a module.

Running worksheet_change event logic can be tricky since you are changing values (formulas) in the procedure each of which trigger the worksheet_change event. This may or may not cause yo ua problem. I woud recommend that you add at the beginning and end...

Application.EnableEvents = False
...' your code
Application.EnableEvents = True

What is NOT updating. The formula or the values calculated by the formula.

I still don't know what you know in your head. What does this mean?

rp  rf  wi  tp  em  total
n u n u n u n u n u  n u 
1 1 1 1 1 1 1 1 1 1  5 5
2 2 2 2 2 2 2 2 2 2 10 10
_________________________
3 3 3 3 3 3 3 3 3 3 15 15

Do you have 7 of these?
The numbers dont add up
What are they?
Where are they? (ranges)



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[angiem]

Thanks for your help, I have got the weekly totals to combine now. What I am doing is I have a worksheet that when a button is selected creates a new worksheet. With the 7 blocks of daily data and the one weekly for the combination of the 7 days.

 

[SkipVought]

You did not answer my questions!

How can I help you if you give me vague, unspecific information???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com