Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Top of the list (Excel 2000)

    Hi all,

    need some help, What I would like to here is test the top of the column for a value, text value.

    Can someone see why this isnt' working?

    Thanks, Darryl.



    sub()

    Selection.End(xlUp).Select
    ActiveCell.Select
    If ActiveCell.Value = "A" Then
    MsgBox ("A")
    ElseIf ActiveCell.Value = "B" Then
    MsgBox ("B")
    ElseIf ActiveCell.Value = "C" Then
    MsgBox ("C")
    End If
    End Sub

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top of the list (Excel 2000)

    It is working (to get the cell at the top).

    I'd pick your cell simply doesn't contain the tested for upper case letters

    Try using Msgbox activecell.value rather than the IFs

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Top of the list (Excel 2000)

    You haven't provided a name for your macro: the first line should be

    Sub NameOfTheMacro()

    In most cases, including this one, it isn't necessary to select cells. Here is a simplified version, assuming that you actually want to write more complicated code:

    Sub GetTopValue()
    Select Case Selection.End(xlUp).Value
    Case "A"
    MsgBox "A"
    Case "B"
    MsgBox "B"
    Case "C"
    MsgBox "C"
    End Select
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Top of the list (Excel 2000)

    Thank you Hans,

    BABY STEPS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Top of the list (Excel 2000)

    Using End(xlUP, xlDown), etc., has drawbacks. Depending on the current Activecell, .End(xlUp) will not take you to the topmost cell if there is an empty cell above it in the same column, and may take you to a title row or rows. Further, if row 1 has content that isn't a title, End(xlDown) may take you past the item you want to look at. Assuming there is no title row, try this variation on Hans code:

    Dim r As Range
    Set r = Cells(1, Selection.Column) ' get the top cell in the column
    If IsEmpty® Then _ ' if it doesn't have anything look below it
    Set r = r.End(xlDown)
    Select Case r.Value
    Case "A"
    MsgBox "A"
    Case "B"
    MsgBox "B"
    Case "C"
    MsgBox "C"
    Case Else
    MsgBox r.Value
    End Select

    if there is a single title row, you'll need to use 'r.Offset(0,1)' to get one row further down. If you'd like to tell more about your objective, there may be better ways to code this. (Understanding about baby steps.)
    -John ... I float in liquid gardens
    UTC -7±DS

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Top of the list (Excel 2000)

    Thank you John for you input.

    My goal with this procedure was to find the top of the list, of three columns, depending on which column the user might be in would load a userform, setting the controls depending onthe top of the list.

    My ulitmate goal though is to have a complete understanding of VBA, I am considerably irked by the fact that some times what I am trying to achieve is in fact simple, and not a road block. I hate to copy others work, yesterday once I posted the original post I tried using a select case statement, without seeing Hans post, I am picking it up, slowly. I appreciate your post, it gives me another way to look at it, and understand just a little more After taking a course and buying a book, I would have thought this would all be second nature.

    I am a little fuzzy on setting objects and variables, until it clicks I'll just keep taking my baby steps

    Thanks
    Darryl.

Posting Permissions

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