Reference changes in a macro or VBA
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Reference changes in a macro or VBA

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com