Range that finds data within it

[kenny87]

Does anyone know how to create an Excel speadsheet in which the range that appears in a cell, for instance 3-5 or 7578-7581, will be will be recognized when an individual uses find to locate a number within the range. So far the only solution I have us to create another spreadsheet in which I type out the numbers in the range across a column. In retrospect I would have to type 3,4,5 in individaul cells. Does anyone know a better solution?

 

[SkipVought]

Hey kenny,

try this...

=IF(AND(H5>=VALUE(LEFT(G5,LEN(G5)-FIND(&quot;-&quot;,G5))),H5<=VALUE(RIGHT(G5,FIND(&quot;-&quot;,G5)-1))),1,0)

where
H5 is the value that you are testing and
G5 contains the numeric range.

hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[kenny87]

I am new to this, so do I use visual basic, macro, what?

 

[Bowers74]

That would be a formula (a MEGA-formula) ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I am still a bit confused as to what you are trying to do though. Could you explaint it a little differently.

Are you trying to find data within ranges 3 and 5 (or 7578 and 7581) instead of searching through the entire column?

A little clarity here! ;-)





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Kenny,

this is a spreadsheet solution to return a single value, 1 if the number is within the range and 0 if it is not. This is not a VBA solution, although it could be coded to be a UserFunction...

Function InRange(rng1 As Range, rng2 As Range) As Boolean
    Dim n1, n2
    With rng1
        n1 = Val(Left(.Value, Len(.Value) - Application.Find("-", .Value)))
        n2 = Val(Right(.Value, Application.Find("-", .Value) - 1))
    End With
    With rng2
        If .Value >= n1 And .Value <= n2 Then
            InRange = True
        Else
            InRange = False
        End If
    End With
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

Another way to do this is:

Function InRange(rng1, rng2) As Boolean
'   Returns True if rng1 is a subset of rng2
    InRange = False
    If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
        If rng1.Parent.Name = rng2.Parent.Name Then
            If Union(rng1, rng2).Address = rng2.Address Then
                InRange = True
            End If
        End If
    End If
End Function

;-)


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[kenny87]

Sorry about that, there is an excel spreadsheet that was previouly created by a memeber of my team. The spreadsheet contains three columns labled "box", "files", and "missing". In the column labeled "box" there is the name of the box (A01). In the cell next to it, under the column labeled "files" there are the range of files that are in the box, for instance (3-7). In the cell next to that, in the column lables "missing", there is a range of any files that are supposed to be in that box that are not. In box (A01) then there would be files (3-7). This pattern repeats itself from box (A01), (A02), to (A25) for each set of boxes, through (ZZ01), (ZZ02), (ZZ25)--all of which are under the column lables "box." The same type of pattern repeats itself in the column labeled "files." For instance, cell A6, in the column labeled "box" with text (A01) would correspond to cell B6, in the column labeled "files" with text (3-7). Likewise, cell A7, in the column labeled "box" with text (A02) would correspond to cell B7, in the column labled "files" with text (8-11). What I want to do is to be able do a find/search for say file "9", then I would want Excel to be able to locate it in the spreadsheet, therefore telling me that I could find the file in box (A02). The dilemma is writing a code that recognizes numbers within a range. That was a mouthful. Thanks for everyones input.

 

[Bowers74]

OK, I still don't understand the "Missing" column yet. You say that the column "Files" states that, for example, files 3-7 are in the box (A01) and that there is a range of files that are missing in the "Missing" column, how does that look?

  Box     Files     Missing
  A01      3-7        2-4
  A02      8-10       8,9
  A03      11-15     12,14
  A04      16-20    16,18-20

or

  Box     Files     Missing
  A01      3-7        2-4
  A02      8-10       8-9
  A03      11-15       12
  A03      11-15       14
  A04      16-20       16
  A04      16-20     18-20

Do you understand the confusion?

Maybe it would be easier to send me a copy of the file, if you want to, or an example of the file's data (in the event that the data is confidential or too large)

vbaprogrammer at etisnikcufecin.com (just change the at to @) ;-)




Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[kenny87]

The confusion is understood, the first example is quite similar to the database, matter of fact it looks the same. The missing column generally contains one or two consecutive missing files, but in 90% of the boxes there are no files missing. But I would think that missing column becomes insignificant in what I am trying to do, b/c, if in your first example, say, I want to find file 16 --Excel locates the range it should fall in--I see it should be in box A04, then I look to the missing column and determine the file has been misplaced.