Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Find last cell "=INDEX(G:G,MATCH(9.99999999999999E+307,G:G))

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

    Default

    I have a spreadsheet that I would like to assign a macro to. So that when I click it it will automatically auto sort a certain column. I am new to this so go easy on me please

    [ This Message was edited by: mapakunk on 2002-05-14 05:45 ]

    [ This Message was edited by: mapakunk on 2002-05-14 06:00 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First, you need to name the column, under Insert/Name/Define.

    Then start to record your macro.
    1st step - Edit/Go To/ type in column name
    2nd step - Data/Sort/whatever order you want
    Stop macro.

    Then add a button to the worksheet via View/Toolbars/Forms/select the button and draw on sheet. When asked to assign a macro, select the one you just recorded.

    Thie above only works if the column of data is always the same size. If you want a dynamic range, the Define Range formula needs to be based on the OFFSET function.

    Hope this helps.

    Nobby

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

    Default

    That works perfectly!!

    Now, how do I rename it from "Button 27" to
    "Outstanding Checks" then I can use this principle from here in all my wonderful spreadsheets. Hee hee hee hu ah ha ha haaaaaaaaaa!

  4. #4
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Right-click on the button and select Edit Text.
    Kristy

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

    Default

    Now one last thing, how can I make a formula that returns the last whole value in column G to a specific cell on the worksheet (look for last entry, then send cell x to c4?)

    [ This Message was edited by: mapakunk on 2002-05-14 05:46 ]

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    please could someone answer the above question.

    Thank you so much

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-09 08:52, mapakunk wrote:
    Now one last thing, how can I make a formula that returns the last whole value in column G to a specific cell on the worksheet (look for last entry, then send cell x to c4?)

    [ This Message was edited by: mapakunk on 2002-05-14 05:46 ]
    If G is of numeric type:

    In C4 enter:

    =OFFSET(G1,MATCH(9.99999999999999E+307,G:G)-1,1,1)

    If G is of alphanumeric type:

    =OFFSET(G1,MATCH(REPT("z"80),G:G)-1,1,1)

    The second formula will ignore the last cell having a formula-generated blank.

    Aladin


Some videos you may like

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
  •