Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert a group of columns, each part of different ranges
Hi All,
I have a points-based gradebook spreadsheet that tracks different daily grades in addition to assignments and tests. For each day, there is a column in which to insert points for an assignment and/or test. To the left of this column are 3 columns: Attend | Perform | Behave | Notes | Activity (orange) (purple) (blue) (green) (no. 0-5) (no. 0-5) (no. 0-5) (text) (any no.) Ranges (=Attndc) (=Prfmnc) (=Behvr) (=Notes) (=AssTest) Could code be made that for every new day, insert these 5, pre-formatted columns to the right of the last column in the 'Assignment or Test' range. Note that a test plus multiple assignments could occur on one day, so there could be multiple columns in the 'Assignment or Test' range. I plan to make formulas that add values in the different numeric ranges. Thanks for any and all help. Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert a group of columns, each part of different ranges
This code inserts 5 columns before the last used column. Perhaps you can
adapt it to your needs. Dim sAdd as String sAdd = Range("IV1").End(xltoLeft).Address Range(sAdd).Resize(,5).Entirecolumn.Insert Range(sAdd).Resize(1,5).Value = Array("Attend","Perform","Behave","Notes","Activit y") Range(sAdd).Offset(1,0).Resize(1,5)=Aray("(orange) ","(purple)","(blue)","(green)","") Range(sAdd).Offset(2,0).Resize(2,5)=Array("(no. 0-5)","(no. 0-5)","(no. 0-5)","(text)","(any no.)") Range(sAdd).Offset(3,0).Resize(3,5)=Array("(=Attnd c)","(=Prfmnc)","(=Behvr)","(=Notes)","(=AssTest)" ) I really don't see anything that would tell me how to find the assignment or test range or to identify a new day or an old day for that matter. I suppose one could use the find method to look for the last occurance of "Activity" in the header row. -- Regards, Tom Ogilvy "Arnold" wrote in message oups.com... Hi All, I have a points-based gradebook spreadsheet that tracks different daily grades in addition to assignments and tests. For each day, there is a column in which to insert points for an assignment and/or test. To the left of this column are 3 columns: Attend | Perform | Behave | Notes | Activity (orange) (purple) (blue) (green) (no. 0-5) (no. 0-5) (no. 0-5) (text) (any no.) Ranges (=Attndc) (=Prfmnc) (=Behvr) (=Notes) (=AssTest) Could code be made that for every new day, insert these 5, pre-formatted columns to the right of the last column in the 'Assignment or Test' range. Note that a test plus multiple assignments could occur on one day, so there could be multiple columns in the 'Assignment or Test' range. I plan to make formulas that add values in the different numeric ranges. Thanks for any and all help. Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert a group of columns, each part of different ranges
Hi Tom,
Just returned and tried some of your code--I got an 'invalid outside procedure.' Range(sAdd).Value=Array("Attend", "Perform" and on and on -- For the Attend column, is this supposed to append each new column with the heading of "Attend" to the previously-named range="Attndc"? This is the functionality it should have--recognizing the column heading (in row 12) and appending that column with other columns with the same heading. Also, new columns should be inserted to the right of the last used column instead of before. Found this code (not a programmer)--would it work? Dim rNextCol As Range Set rNextCol = ActiveCell.End(xlToRight).Offset(0, 1) or LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRang e.Columns.count).Column Columns(LastColumn + 1).Select One better than the other? Lost; thanks; Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert a group of columns, each part of different ranges
If it meets your needs, use it. You understand your needs better than I do.
-- Regards, Tom Ogilvy "Arnold" wrote in message oups.com... Hi Tom, Just returned and tried some of your code--I got an 'invalid outside procedure.' Range(sAdd).Value=Array("Attend", "Perform" and on and on -- For the Attend column, is this supposed to append each new column with the heading of "Attend" to the previously-named range="Attndc"? This is the functionality it should have--recognizing the column heading (in row 12) and appending that column with other columns with the same heading. Also, new columns should be inserted to the right of the last used column instead of before. Found this code (not a programmer)--would it work? Dim rNextCol As Range Set rNextCol = ActiveCell.End(xlToRight).Offset(0, 1) or LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRang e.Columns.count).Column Columns(LastColumn + 1).Select One better than the other? Lost; thanks; Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I remove part of a file name (LO) from a group of excel files | Excel Discussion (Misc queries) | |||
Using Named Ranges with Charts: Part 2 (!) | Excel Worksheet Functions | |||
How do I name a group of ranges? | Excel Discussion (Misc queries) | |||
unable to insert columns in excel, insert- columns (disabled) | Excel Discussion (Misc queries) | |||
Renamed ranges - PART 2 | Excel Programming |