How to insert a group of columns, each part of different ranges

[Arnold]

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

 

[Tom Ogilvy]

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","Activity")
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("(=Attndc)","(=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.

 

[Arnold]

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.UsedRange.Columns.count).Column

Columns(LastColumn + 1).Select

One better than the other?

Lost; thanks;

Eric

 

[Tom Ogilvy]

If it meets your needs, use it. You understand your needs better than I do.