Reference changes in a macro or VBA

JohnJay

New Member
Joined
Mar 7, 2002
Messages
37
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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,
 
Upvote 0
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.
 
Upvote 0
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"):
Code:
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?
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
John, sorry for the wild goose chase - I guess I must be getting old :oops:

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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top