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. ## 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. ## 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.
Smbs

4. ## 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
Else
tLowerCell=Cells(WksFns.Match(Val(tLowerBound), Range("Data"))+iDataRow-1, _
End If

If Val(tUpperBound) < Range("Data").Cells(1, 1) Then
Else
tUpperCell=Cells(WksFns.Match(Val(tUpperBound), Range("Data"))+iDataRow-1, _
If Range(tUpperCell) < Val(tUpperBound) Then _
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. ## Re: cell/range address (97+)

Thanx will give it a try
Smbs

6. ## 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
•