jimmyyy
11-30-2006, 01:40 AM
To sum it up i make a macro then months later end up inserting a column or two in the worksheet, and the macro no longer associates with the field, but stays on the range or cell. For example the macro is filtering say column ?H:H? which is fine but if i insert a new column in were column ?D? is, the macro no longer works. It tays on ?H:H? but the field has moved over one ?I:I?. Am i supposed to name give the range?
MY 2 YEAR ITCH:moosegrin
a very basic problem i cant figure out. For quite some time ive yet to discover, is been a itch ive yet to scratch.
In Excel VB I CREATE A MACRO (many), Which works great. But changes in business thought the yr push me to add additional columns thought the yr. Well in excel I have to tag them on to the end of my database field in order to not mess up the macro. But they usually don?t go well at the end of the data field, but need to group up next to like fields, I think its just a matter of naming the cells, ranges, or fields? Hears a super simple example below, pardon my baby steps.
Sub Filter_n_Sum()
Selection.AutoFilter Field:=8, Criteria1:="JM" ' THIS FILTERS COLUMN 'H' IN EXCEL( this ?Field:8? creates a problem later)
Cells.Select
Selection.Copy
Sheets("Stats").Select
Range("A1").Select
ActiveSheet.Paste
Range("J2:J58").Select
Range("J58").Activate ' THIS SELECTS THE RANGE TO SUM , ( AGAIN HERE I WOULD RATHER GIVE THE RANGE OF CELLS some type of refferance.)
ActiveCell.FormulaR1C1 = "=SUM(R[-56]C:R[-1]C)"
Columns("J:J").ColumnWidth = 10.5 ' HOW DO I SET "J:J" TO = A NAME, THEN LATER IF I DESIRED I COULD INSERT A NEW COLUMN or FIELD i.e. WERE COLUMN B WAS, IT will push all the fields down but that WOULDNT EFFECT THE MACRO BECUASE ITS ATTACHED TO A NAME or reference INSTEAD OF column "J:J" OR IN THE CASE OF THE FILTER 'Field:=8'
End Sub
' ANY HELP IN LAYMANS TERMS IS HUGLY APPRECIATED.
ps this is my first post hopefully its accurate
pss what is word perfect anyways?:rofl:
MY 2 YEAR ITCH:moosegrin
a very basic problem i cant figure out. For quite some time ive yet to discover, is been a itch ive yet to scratch.
In Excel VB I CREATE A MACRO (many), Which works great. But changes in business thought the yr push me to add additional columns thought the yr. Well in excel I have to tag them on to the end of my database field in order to not mess up the macro. But they usually don?t go well at the end of the data field, but need to group up next to like fields, I think its just a matter of naming the cells, ranges, or fields? Hears a super simple example below, pardon my baby steps.
Sub Filter_n_Sum()
Selection.AutoFilter Field:=8, Criteria1:="JM" ' THIS FILTERS COLUMN 'H' IN EXCEL( this ?Field:8? creates a problem later)
Cells.Select
Selection.Copy
Sheets("Stats").Select
Range("A1").Select
ActiveSheet.Paste
Range("J2:J58").Select
Range("J58").Activate ' THIS SELECTS THE RANGE TO SUM , ( AGAIN HERE I WOULD RATHER GIVE THE RANGE OF CELLS some type of refferance.)
ActiveCell.FormulaR1C1 = "=SUM(R[-56]C:R[-1]C)"
Columns("J:J").ColumnWidth = 10.5 ' HOW DO I SET "J:J" TO = A NAME, THEN LATER IF I DESIRED I COULD INSERT A NEW COLUMN or FIELD i.e. WERE COLUMN B WAS, IT will push all the fields down but that WOULDNT EFFECT THE MACRO BECUASE ITS ATTACHED TO A NAME or reference INSTEAD OF column "J:J" OR IN THE CASE OF THE FILTER 'Field:=8'
End Sub
' ANY HELP IN LAYMANS TERMS IS HUGLY APPRECIATED.
ps this is my first post hopefully its accurate
pss what is word perfect anyways?:rofl: