Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing an Excel cell

    Does anyone know how to access, the last value, in an Excel cell

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Accessing an Excel cell

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> glukhurst

    Typically the Validation list is known to be a range, or maybe some array in VBA.

    If by last value you mean value # 5 in a 5-value list, then you can do:

    1) If the list is in a range say in "E1:E5" use this:
    <font color=red> lLastValue = Range("E1:E5").Cells(Range("E1:E5").Cells.Count).V alue </font color=red>

    2) If the list is in an array say called ListOfValidEntries then use this:
    <font color=red> lLastValue = ListOfValidEntries(UBound(ListOfValidEntries)) </font color=red>

    I hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

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

    Re: Accessing an Excel cell

    Gluckhurst

    It looks to be especially long-winded

    Range("A1").Validation returns the Validation object

    If you knew you were dealing with a list, I think you could just use

    .Validation.Formula1 which holds the array as a comma delimited list, or as a rangename - I've never tried decoding Formula1, although I have set it.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing an Excel cell

    As the formula1 property of the validation method returns the range address, you can use the code below to find the last value in a validation list:

    Assume here that you have data to enter in the range B2:B10 and that a validation list is used for this. Then Range("B2:B10").Validation.Formula1 returns the range address of the validation list.

    <pre>Sub test()
    Dim n As Integer
    Dim ValAddress As String
    ValAddress = Range("B2:B10").Validation.Formula1
    n = Range(ValAddress).Cells.Count
    MsgBox Range(ValAddress).Cells(n).Value
    End Sub
    </pre>


    Hope this helps.

  5. #5
    Lounger
    Join Date
    Jan 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing an Excel cell

    Many thanks

    This has put me on the right track.

Posting Permissions

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