Find all and Sort 2

[johnny45]

I'm using this piece of code to find all invoice lines on a sheet to be able to re display the invoice.
col A is the type
col B is invoice number
col C is line number
col D is the line detail
col E is the dollar amount

'[URL unfurl="true"]http://www.cpearson.com/excel/RangeFind.htm[/URL]

Function FindAll(SearchRange As Range, FindWhat As Variant, _
    Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, _
    Optional SearchOrder As XlSearchOrder = xlByRows, _
    Optional MatchCase As Boolean = False) As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FindAll
' This returns a Range object that contains all the cells in SearchRange in which FindWhat
' was found. The parameters to the function have the same meaning as they do for the
' Find method of the Range object. If no cells were found, the result of this function
' is Nothing.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim FoundCell As Range
Dim FoundCells As Range
Dim lastcell As Range
Dim FirstAddr As String
With SearchRange
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' In order to have Find search for the FindWhat value
    ' starting at the first cell in the SearchRange, we
    ' have to find the last cell in SearchRange and use
    ' that as the cell after which the Find will search.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set lastcell = .Cells(.Cells.Count)
End With

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Do the initial Find. If we don't find FindWhat in the first Find,
' we won't even go into the code which searches for subsequent
' occurances.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set FoundCell = SearchRange.Find(what:=FindWhat, after:=lastcell, _
    LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
If Not FoundCell Is Nothing Then
    ''''''''''''''''''''''''''''''
    ' Set the FoundCells range
    ' to the first FoundCell.
    ''''''''''''''''''''''''''''''
    Set FoundCells = FoundCell
    ''''''''''''''''''''''''''''
    ' FirstAddr will contain the
    ' address of the first found
    ' cell. We test each FoundCell
    ' to this address to prevent
    ' the Find from looping back
    ' through the range it has
    ' already searched.
    ''''''''''''''''''''''''''''
    FirstAddr = FoundCell.Address
    Do
        ''''''''''''''''''''''''''''''''
        ' Loop calling FindNext until
        ' FoundCell is nothing or
        ' we wrap around the first
        ' found cell (address is in
        ' FirstAddr).
        '''''''''''''''''''''''''''''''
        Set FoundCells = Application.Union(FoundCells, FoundCell)
        Set FoundCell = SearchRange.FindNext(after:=FoundCell)
    Loop Until (FoundCell Is Nothing) Or (FoundCell.Address = FirstAddr)
End If

''''''''''''''''''''
' Return the result.
''''''''''''''''''''
If FoundCells Is Nothing Then
    Set FindAll = Nothing
Else
    Set FindAll = FoundCells
End If
End Function
Sub TestFindAll()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TestFindAll
' This is a test procedure for FindAll.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim SearchRange As Range
    Dim FoundCells As Range
    Dim FoundCell As Range
    Dim FindWhat As Variant
    Dim MatchCase As Boolean
    Dim LookIn As XlFindLookIn
    Dim LookAt As XlLookAt
    Dim SearchOrder As XlSearchOrder
    
    ''''''''''''''''''''''''''
    ' Set the variables to the
    ' appropriate values.
    ''''''''''''''''''''''''''
    Set SearchRange = Sheet4.Range("B:B")
    FindWhat = "1000"
    LookIn = xlValues
    LookAt = xlWhole
    SearchOrder = xlByRows
    MatchCase = False
    
    '''''''''''''''''''
    ' Search the range.
    '''''''''''''''''''
    Set FoundCells = FindAll(SearchRange:=SearchRange, FindWhat:=FindWhat, _
        LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    
    ''''''''''''''''''''''
    ' Display the results.
    ''''''''''''''''''''''
    If FoundCells Is Nothing Then
        Debug.Print "No cells found."
    Else
        For Each FoundCell In FoundCells.Rows
'        MsgBox FoundCell.Row
            Debug.Print FoundCell.Address, FoundCell.Text
        Next FoundCell
    End If
End Sub

The code works great in finding ALL the addresses containing the invoice number.
However how would I :
Sort the found range by line number and then how would I loop though it to re build my invoice ?

I was thinking of a multi dimensional array...but not sure it is the best way to go...

 

[Bong]

The FindAll function returns a range. From the looks of the code, the range it returns will be in the column that contains the FindWhat argument (column B in your application). From there, build a range that goes from Column A, first row in the returned range (<range>.cells(1).row), to Column E, last row in the returned range (<range>.cells(<range>.cells.count).row). Then sort that range on Column C (see the Excel VBA help)

expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

where Key1 will be cells(1,3)

_________________
Bob Rashkin

 

[johnny45]

Thanks for the quick reply...

This is very new to me...(setting ranges in this maner)

Something like this :
set newrange=("A"&FoundCells.cells(1).row),"E"&FoundCells.cells(FoundCells.cells.count).row)) ?


newrange.Sort Key1:=Cells(1, 3), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

 

[Bong]

It looks right (although I think you need a space between "&" and "FoundCells.cells(1).row"). Does it work?

_________________
Bob Rashkin

 

[johnny45]

no does not work I put the spaces but the comma is giving me the error

 

[Bong]

should be a colon )

_________________
Bob Rashkin

 

[SkipVought]

Hi,

with FoundCells
  Set newrange = _
    Range(Cells(.Row, "A"), Cells(.Rows.Count + .Row - 1, "E"))
end with

or

Set newrange = _
  Intersect(FoundCells.entirerow, Range("A:E").entirecolumn)
end with

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[johnny45]

Thank you both for the replies.
FYI putting a : did not work..
My data could typically look like this:

Type Number Line Num Detail Amount
Quote 1000 2 detail 2 200
Quote 1000 4 detail 4 100
Invoice 1001 1 detail 1 40
Invoice 1001 3 detail 3 600
Invoice 1001 4 detail 4 100
Quote 1000 3 detail 3 40
Invoice 1001 2 detail 2 400
Quote 1000 1 detail 1 30

The findAll debug print gives me this :
$B$2 1000
$B$3 1000
$B$7 1000
$B$9 1000

which is correct

However I'm not sure that assigning this to a new range does the trick as it is non continous....

I would have liked to be able to sort the found rows by line num so I could then loop through them to re build the invoice

Skip,

with FoundCells
  Set newrange = _
    Range(Cells(.Row, "A"), Cells(.Rows.Count + .Row - 1, "E"))
end with

produced this:
$A$2:$E$2
$A$3:$E$3

However,

Set newrange = _
  Intersect(FoundCells.entirerow, Range("A:E").entirecolumn)
end with

produced this:
$A$2:$E$2
$A$3:$E$3
$A$7:$E$7
$A$9:$E$9
which is correct ok.

the sheet containing the data is sheet 4.
is it easiest just to sort the sheet B 4 doing the find all so that the found range is sorted and continous?

 

[SkipVought]

So FoundCells is not a contiguous range.

Of course the first one will not work.

The "colon" method will not work either. The colon will produce a CONTIGUOUS range.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}