Solved: Another sorting problem

[blackie42]

Hi,

I need to sort a long list of accounts and am a bit stuck as to how to go about it.

I need to identify the word 'stopped' in col A and then if there is a number in the row above, col C then keep the infos in the 6 cells.

Have attached a test spreadsheet to demonstrate. Highlighted in Yellow

Be really grateful for any help

thanks

Jon

[Oorang]

Try this:
Option Explicit

Public Sub Example()
Const dblNoValue_c = 0#
Dim wsMidasReport As Excel.Worksheet
Dim wsOutput As Excel.Worksheet
Dim rngExamine As Excel.Range
Dim cll As Excel.Range
Dim strValue As String
Dim lngOutputRow As Long
Dim dblNumber As Double
'Get reference to sheet that has report:
Set wsMidasReport = Excel.ActiveSheet
'Create Output Sheet:
Set wsOutput = Excel.Workbooks.Add.Worksheets(1)
wsOutput.Name = Left$("Output-" & wsMidasReport.Name, 31)
'Set range to look at for the word "Stopped" to column 1.
Set rngExamine = wsMidasReport.Columns(1)
'Eliminate empty cells under bottom row from range:
Set rngExamine = Excel.Intersect(rngExamine, wsMidasReport.UsedRange)
For Each cll In rngExamine.Cells
'Get value of cell:
strValue = cll.Value
'Precheck cell to make sure it is not empty (optimizing trick to improve
'speed).
If LenB(strValue) Then
'If cell has value removed lead/trailing spaces so they will be
'ignored for comparison. Force lowercase so comparison is NOT
'case sensitive:
strValue = LCase$(Trim$(strValue))
'See if cleanup value is equal to "stopped":
If strValue = "stopped" Then
'If so, check the offset to see if the value is numeric
'(prevents errors caused trying to load in letters in the event
'of a typo).:
If IsNumeric(cll.Offset(-1, 2).Value) Then
'Value is numeric. Get Value for comparison:
dblNumber = CDbl(cll.Offset(-1, 2).Value)
'If number is not Zero then send to output sheet (make
'comparison ">" to eliminate negative values):
If dblNumber <> dblNoValue_c Then
'Increment Output Row:
lngOutputRow = lngOutputRow + 1
'Send data to output sheet:
wsOutput.Cells(lngOutputRow, 1).Value = cll.Offset(-1, 0).Value
wsOutput.Cells(lngOutputRow, 2).Value = cll.Offset(-1, 1).Value
wsOutput.Cells(lngOutputRow, 3).Value = cll.Offset(-1, 2).Value
wsOutput.Cells(lngOutputRow, 4).Value = cll.Offset(0, 0).Value
wsOutput.Cells(lngOutputRow, 5).Value = cll.Offset(0, 1).Value
wsOutput.Cells(lngOutputRow, 6).Value = cll.Offset(0, 2).Value
End If
End If
End If
End If
Next
End Sub

[blackie42]

Thanks very much for the code - it works well.

I have another favour to ask - can it be extended to add another column and 'indicator' - I took out a cloumn that needs to be in and marked as 'Y'

So my criteria are 'stopped' in col A , letter 'Y' in col C and numbers in col D (both a row above the stopped.

thanks again

Jon

[blackie42]

So I marked the ones in blue that fit the first criteria but not the second - just the yellows