Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cell/range address (97+)

    I have a large spreadsheet containing numbers in columns ascending sorted ie column 1 row 5 has 1 in it column 1 row 6 will have a value larger than 1 in it etc.
    I need a script which will output starting cell address and final cell address with input being the smallest number to find and the second number being the largest number to find . I need the cell range/address of the 2 numbers.
    Hope I have made myself clear
    TIA
    Smbs

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell/range address (97+)

    You could a) write a macro to prompt a user to input the 2 numbers, or [img]/forums/images/smilies/cool.gif[/img] you could type the 2 numbers into 2 cells and have 2 formulas that return the addresses.

    I would prefer method [img]/forums/images/smilies/cool.gif[/img] since you don't have to run a macro.

    If your data is in cells a5:a100 and the small number is A1 and the big number is in A2 then

    =address(match(a1,a5:a100)+row(a5)-1,column(a5)) will give you the address of the last cell <= a1.

    =address(match(a2,a5:a100)+row(a5)-if(isna(match(a2,a5:a100,0)),0,1),column(a5)) will give you the address of the first cell >= a2

    If you really want a macro, just reply and I (or someone else) can whip it up pretty quickly.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell/range address (97+)

    Thanx Chipshot ---got the idea however
    I would like up to "whip up a quick macro" as per your quote.
    Thanx again for your quick reply
    Smbs

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell/range address (97+)

    Okay, how does this grab you?
    <pre>Option Explicit
    Sub SpanningRange()
    Dim tLowerBound, tUpperBound As String
    Dim tLowerCell, tUpperCell As String
    Dim iDataRow, iDataCol As Integer
    Dim WksFns As Object
    Set WksFns = Application.WorksheetFunction

    iDataRow = Range("Data").Row
    iDataCol = Range("Data").Column

    tLowerBound = InputBox("Input Lower Bound")
    tUpperBound = InputBox("Input Upper Bound")

    If Val(tLowerBound) < Range("Data").Cells(1, 1) Then
    tLowerCell = Cells(iDataRow, iDataCol).Address
    Else
    tLowerCell=Cells(WksFns.Match(Val(tLowerBound), Range("Data"))+iDataRow-1, _
    iDataCol).Address
    End If

    If Val(tUpperBound) < Range("Data").Cells(1, 1) Then
    tUpperCell = Cells(iDataRow, iDataCol).Address
    Else
    tUpperCell=Cells(WksFns.Match(Val(tUpperBound), Range("Data"))+iDataRow-1, _
    iDataCol).Address
    If Range(tUpperCell) < Val(tUpperBound) Then _
    tUpperCell = Range(tUpperCell).Cells(2, 1).Address
    End If

    MsgBox "Your input bounds of " + tLowerBound + " and " _
    + tUpperBound + " are spanned by cells " + tLowerCell + " and " + tUpperCell
    End Sub
    </pre>


    This assumes the column you're interested in is named Data. It may or may not give you the desired answer when one or both of your bounds are less than or greater than all of the data.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell/range address (97+)

    Thanx will give it a try
    Smbs

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell/range address (97+)

    Works great many thanx Chipshot!
    smbs

Posting Permissions

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