Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Omit/bypass cells in column equations
Hello! I have a worksheet macro as follows:
Sub InsertNewRow() ActiveSheet.Unprotect Rows("10:10").Select Selection.Insert Shift:=xlDown Range("10:10").Select Selection.Locked = True Range("11:11").Select Selection.Locked = False ActiveSheet.Protect End Sub The of cells from and inclusive $D$11 to $D$15 are named 'Recent' The of cell s from and inclusive $D$11 to $D$65536 are named 'EvalTotal' I need to change this to make cells D6 display the 'Recent' Average and D7 to display the 'EvalTotal' Average My problems are as follows: 1) When I insert a new row using the macro shown above cell numbers change for 'Recent' to $D$12 to $D$16 and 'EvalTotal' to $D$12 to $D$65536 2) If the cell range for 'Recent' and 'EvalTotal' change-then the results for 'Recent Average' and 'EvalTotal Average' will not reflect an accurate conclusion I have tried to explain this clearly but it is difficult to do so. Please ask for further clarification if required to lead to any helpful suggestions Cheers Sherry |
#2
|
|||
|
|||
If you change the definition of the Recent range to
=OFFSET(Sheet1!$D$6,5,0,5,1) and change EvalTotal to =OFFSET(Sheet1!$D$6,5,0,65526,1) they will be impervious to the changes from InsertNewRow Alternatively, you could redefine you ranges in the macro, after the row insertion "Sherry" wrote: Hello! I have a worksheet macro as follows: Sub InsertNewRow() ActiveSheet.Unprotect Rows("10:10").Select Selection.Insert Shift:=xlDown Range("10:10").Select Selection.Locked = True Range("11:11").Select Selection.Locked = False ActiveSheet.Protect End Sub The of cells from and inclusive $D$11 to $D$15 are named 'Recent' The of cell s from and inclusive $D$11 to $D$65536 are named 'EvalTotal' I need to change this to make cells D6 display the 'Recent' Average and D7 to display the 'EvalTotal' Average My problems are as follows: 1) When I insert a new row using the macro shown above cell numbers change for 'Recent' to $D$12 to $D$16 and 'EvalTotal' to $D$12 to $D$65536 2) If the cell range for 'Recent' and 'EvalTotal' change-then the results for 'Recent Average' and 'EvalTotal Average' will not reflect an accurate conclusion I have tried to explain this clearly but it is difficult to do so. Please ask for further clarification if required to lead to any helpful suggestions Cheers Sherry |
#3
|
|||
|
|||
OKay Hmmm!
Thanks Duke! I think lol! I have no idea what that means but I am trialing it now!!! and attempting to grasp what actions it is actually perfoprming in it execution and.......perhaps even more importantly, how and why it bloody works! So to rate your post would be premeture although I am sure it WILL prove to be a valuable insite it this EXCEL programming corridoor! Gee I will be glan when i see the light at the end of THIS corridoor! Guess I should have included that I am absolute rank ameture and to compound matters I am pathetic at maths (Seems spelling fits that catagory too lol) Soooory! I'll get back to u but thank u so far!............ Cheers Sherry "Duke Carey" wrote: If you change the definition of the Recent range to =OFFSET(Sheet1!$D$6,5,0,5,1) and change EvalTotal to =OFFSET(Sheet1!$D$6,5,0,65526,1) they will be impervious to the changes from InsertNewRow Alternatively, you could redefine you ranges in the macro, after the row insertion "Sherry" wrote: Hello! I have a worksheet macro as follows: Sub InsertNewRow() ActiveSheet.Unprotect Rows("10:10").Select Selection.Insert Shift:=xlDown Range("10:10").Select Selection.Locked = True Range("11:11").Select Selection.Locked = False ActiveSheet.Protect End Sub The of cells from and inclusive $D$11 to $D$15 are named 'Recent' The of cell s from and inclusive $D$11 to $D$65536 are named 'EvalTotal' I need to change this to make cells D6 display the 'Recent' Average and D7 to display the 'EvalTotal' Average My problems are as follows: 1) When I insert a new row using the macro shown above cell numbers change for 'Recent' to $D$12 to $D$16 and 'EvalTotal' to $D$12 to $D$65536 2) If the cell range for 'Recent' and 'EvalTotal' change-then the results for 'Recent Average' and 'EvalTotal Average' will not reflect an accurate conclusion I have tried to explain this clearly but it is difficult to do so. Please ask for further clarification if required to lead to any helpful suggestions Cheers Sherry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
copy every 30th cell in the column into consecutive cells in anoth | Excel Worksheet Functions | |||
ADDING SUM TOTAL OF MORE THAN 30 CELLS IN A COLUMN TOGETHER - WON. | Excel Discussion (Misc queries) | |||
Return non-zero cells in column | Excel Worksheet Functions |