Reference changes in a macro or VBA

[JohnJay]

I am writing macros & VBA functions to do various tasks. But I wish to have cell references in the macros & VBA functions to be updates when a column or row is added to a sheet. Can anyone shed light on this?

Thank you

 

[JohnJay]

Barrie,

IT does not like the Range(Cells(1, SortColumn))

I was thinking along the lines of

Key1:=Range(Chr(65 + SortColumn) & "2")

But that will only work if "Divison" is between A & Z

 

[Barrie Davidson]

On 2002-03-08 12:31, JohnJay wrote:
Barrie,

IT does not like the Range(Cells(1, SortColumn))

I was thinking along the lines of

Key1:=Range(Chr(65 + SortColumn) & "2")

But that will only work if "Divison" is between A & Z

John, I really must apologize for not testing this properly (I know, haste makes waste). How about:

Sub SortData()
Dim SortColumn As Integer, LastColumn As Integer
Dim LastRow As Long
LastColumn = Range("A1").End(xlToRight).Column
LastRow = Range("A65536").End(xlUp).Row
On Error GoTo ErrorHandler
SortColumn = Rows("1:1").Find(What:="Division", _
    LookAt:=xlWhole).Column
On Error GoTo 0
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Sort _
    Key1:=Range(Cells(1, SortColumn).Address(False, False)), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
Exit Sub
ErrorHandler:
    MsgBox prompt:="Could not find a header labelled DIVISION" _
        & Chr(13) & "Data was not sorted", _
        Buttons:=vbCritical + vbOKOnly
End Sub

Let's hope I've finally got this pesky thing right

 

[JohnJay]

Barrie,

your a genius...thank you very much. Many blessing to you. If I can pick your brain for something else, how are you on the rank function?

 

[Barrie Davidson]

On 2002-03-08 12:59, JohnJay wrote:
Barrie,

your a genius...thank you very much. Many blessing to you. If I can pick your brain for something else, how are you on the rank function?

I am familiar with it (although I wouldn't want to hazard a guess as to the extent of my familiarity). What do you want to know?

 

[JohnJay]

Check out this post

http://www.mrexcel.com/board/viewtopic.php?topic=1697&forum=2&0