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

[Barrie Davidson]

On 2002-03-08 10:42, JohnJay wrote:
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

To identify columns, do you have a header row? If yes, you could search for a specific value and, when found, set your column at that point.

For your rows, I assume you want to be able to find the last row. If that is the case, you could use something like:

Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("D" & LastRow).Select

This would select the cell in column D at the last row of your data (assuming that column A always contains data).

Regards,

[JohnJay]

Barrie, actually what I am doing is trying to set up fuctions that do specific tasks to a sheet. So say I write a macro to perform sort on column K, but the user later adds a column before anywhere before K. Now the macro will sort what was column J before the insert. I hope this gives you a better understanding. By the way, I am using Headers, but the names are always subject to change....Unless I dont alow that.

[Barrie Davidson]

On 2002-03-08 11:14, JohnJay wrote:
Barrie, actually what I am doing is trying to set up fuctions that do specific tasks to a sheet. So say I write a macro to perform sort on column K, but the user later adds a column before anywhere before K. Now the macro will sort what was column J before the insert. I hope this gives you a better understanding. By the way, I am using Headers, but the names are always subject to change....Unless I dont alow that.

Are you able to lock the header values? If so, you could use something like this (that sorts on the header labelled "JohnJay"):

Dim SortColumn As Integer, LastColumn As Integer
Dim LastRow As Long
LastColumn = Range("A1").End(xlToRight).Column
LastRow = Range("A65536").End(xlUp).Row
SortColumn = ActiveSheet.Find(what:="JohnJay", _
    LookAt:=xlWhole).Column
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Sort _
    Key1:=Range("A" & SortColumn), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom

Does this help you?

[JohnJay]

Barrie, I am getting your idea, and I think it may work. Couple of questions though...
How can I lock the header, and the following part of your code is giving me an error in VBA

SortColumn = ActiveSheet.Find(what:="Division", _
LookAt:=xlWhole).Column

[Barrie Davidson]

On 2002-03-08 11:35, JohnJay wrote:
Barrie, I am getting your idea, and I think it may work. Couple of questions though...
How can I lock the header, and the following part of your code is giving me an error in VBA

SortColumn = ActiveSheet.Find(what:="Division", _
LookAt:=xlWhole).Column

Now that I think about it, you won't be able to lock the header and allow the user to insert a column. I was thinking you could lock the header row and then protect the worksheet, but if you protect the worksheet the user won't be able to insert a column. How about slightly changing the code to:

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 = ActiveSheet.Find(what:="Division", _
LookAt:=xlWhole).Column
On Error GoTo 0
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Sort _
Key1:=Range("A" & SortColumn), _
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

This will sort on "Division" and, if it's not found, will return an error message.

As for the error message you received, does "Division" exist in the spreadsheet. The macro is searching for an exact match to that word.

Regards,

[JohnJay]

Barrie,

The error I get back when

"SortColumn = ActiveSheet.Find _(what:="Division", LookAt:=xlWhole).Column"

is executed is, "Object doesn't support this property or method (Error 438)" The error check that you put in seems like a great idea, the only thing is that it caught this error as "Division" not found.

[Barrie Davidson]

On 2002-03-08 11:50, JohnJay wrote:
Barrie,

The error I get back when

"SortColumn = ActiveSheet.Find _(what:="Division", LookAt:=xlWhole).Column"

is executed is, "Object doesn't support this property or method (Error 438)" The error check that you put in seems like a great idea, the only thing is that it caught this error as "Division" not found.

John, good thing you're patient.
Let's try this code instead:

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("A" & SortColumn), _
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

I changed where the macro searches for "Division". "ActiveSheet" was an invalid object for the "Find" function and I further changed it to only search the first row (your headers) just in case the word "Division" might exist somewhere else in your data.

How close are we now?

[JohnJay]

ok barrie, the find for the column worked great..One other problem. The sort key is ""A" & Sortcolumn" Which if my "Division" is in Column 11, the Key gives All. What I would like to happen is that it sorts the whole sheet according to the "Division" column, minus the header of course.

thanks a bunch for your help

[Barrie Davidson]

John, sorry for the wild goose chase - I guess I must be getting old

Change that statement to read (I changed the Key1 part):
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Sort _
Key1:=Range(Cells(1, SortColumn)), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


Now, it's time I wake up and pay attention.