Macros

[ironsides]

I use a lot of macros. Occasionally after setting up a new sheet with macros I find that I neglected to include a field in my sheet. After I add that field, I have to manually change every macro that contains a field to the right of that newly inserted field. Is there a better way? Im using Excel 2000. Does the latest version overcome this? Thanks

 

[Smitty]

I suppose we'd really have to see your code and what your definition of a "field" is.

Smitty

 

[ironsides]

Field is a column

Here are 2 typical codes:

Sub GIZ()
'
' GIZ Macro
' Macro recorded 10/8/2006 by USER
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Selection.Sort Key1:=Range("n7"), Order1:=xlAscending, Key2:=Range("p7") _
, Order2:=xlAscending, Key3:=Range("q7"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

And


Sub SpG()
'
' Spacer Macro
' Macro recorded 5/23/2006 by USER
'
' Keyboard Shortcut: Ctrl+q
'

'
' SpG Macro
' Macro recorded 5/23/2006 by USER
'
' Keyboard Shortcut: Ctrl+q
'

Dim i As Long
For i = Range("b65536").End(xlUp).Row To 5 Step -1
If Cells(i, 14) <> Cells(i - 1, 14) Then Rows(i).EntireRow.Insert
Next i

End Sub

 

[PaddyD]

"Is there a better way? "

Yep - it's called learning the vba object model (especially the range object) so you can generalise your code from what the macro recorder does to more useful subs etc.

have a look here & see how you get on:

http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

 

[HalfAce]

Another idea (after learning what Paddy & Tushar are trying to show you here of course) :wink:
might be to look into using named ranges and referring to them in your code instead of
using fixed range references.