PDA

View Full Version : Solved: Find all occurrences in col T



Rob342
08-14-2012, 03:04 PM
I am trying to find all Occurrences of "I" in col T and transfer the found line to Sheet3 .
I found this "Find all" Function, but it doesn't work, any ideas why?

Function FindAll(SearchRange As Range, _
FindWhat As Variant, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional SearchOrder As XlSearchOrder = xlByRows, _
Optional MatchCase As Boolean = False, _
Optional BeginsWith As String = vbNullString, _
Optional EndsWith As String = vbNullString, _
Optional BeginEndCompare As VbCompareMethod = vbTextCompare) As Range
End Function

Sub TestFindAll()

Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Dim ws As Worksheet

Set ws = Worksheets("MainDB")
With ws
Set SearchRange = Range("T3:T100") ' to be extended to lastrow later
FindWhat = "I"
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
MsgBox "Value Not Found"
Else
For Each FoundCell In FoundCells
MsgBox "Value Found In Cell: " & FoundCell.Address(False, False)
Next FoundCell
End If
End With
End Sub

mancubus
08-14-2012, 03:41 PM
at first glance, you only copied function parameters.

http://www.cpearson.com/excel/FindAll.aspx

scroll down the page to see the entire code...

Rob342
08-15-2012, 12:36 AM
Thanks Mancubus

One of those late nights, didn't read it properly.
just need to pull in the data now
Rob

snb
08-15-2012, 04:05 AM
Alternative:

Sub snb()
sn = Filter([transpose(if(Sheet1!T1:T200="I",row(1:200),"~"))], "~", False)

With Sheet1.Cells(1).CurrentRegion
Sheet3.Cells(1).Resize(UBound(sn) + 1, .Columns.Count) = Application.Index(.Value, Application.Transpose(sn), Evaluate("column(1:" & .Columns.Count & ")"))
End With
End Sub

Rob342
08-15-2012, 05:21 AM
snb
Is this for pulling in the data to sheet 3, what sn dim as, also asking for expected array on this line. (UBound)

snb
08-15-2012, 06:04 AM
Yes the code is writing the result to sheet3 (if that is the correct name: you'll have to check in your workbook).
Remove Option explicit to avoid unnecessary warnings.

Bob Phillips
08-15-2012, 06:20 AM
Or do the sensible thing and Dim it as a variant.

mancubus
08-15-2012, 06:58 AM
if you're wanting to copy matching rows on column T from Sheet1 to Sheet3 then, as an alternative...


Sub CopyMatchedRows()

Dim rngAF As Range

With Sheet1.Cells(1).CurrentRegion
.AutoFilter Field:=20, Criteria1:="I"
End With

Sheet3.Cells.Clear
With Sheet1.AutoFilter.Range
If .Rows.Count = 1 Then
MsgBox "No match, quitting..."
Exit Sub
End If
Set rngAF = .SpecialCells(xlCellTypeVisible) 'with header row
'Set rngAF = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12) 'without header row
rngAF.Copy Sheet3.Cells(1)
End With

Sheet1.AutoFilterMode = False

End Sub

mancubus
08-15-2012, 07:01 AM
@snb

it throws "rte 13, type mismatch"

snb
08-15-2012, 08:53 AM
Cfr. the attachment

mancubus
08-15-2012, 01:02 PM
Cfr. the attachment

thanks for the working example.

now i wonder why i didn't work with my sample data... will try again tomorrow...

mancubus
08-15-2012, 01:15 PM
thanks to snb for this shorter filter & copy method.


Sub CopyMatchedRows()

Sheet3.Cells.Clear

With Sheet1.Cells(1).CurrentRegion
.AutoFilter 20, "I"
.Offset(1).Copy Sheet3.Cells(1)
.AutoFilter
End With

End Sub

Rob342
08-15-2012, 03:08 PM
snb & Mancubus
I added dim sn as variant, worked ok

One last question, is it possible to chk on col T for "I" and then only copy the data in from col "B" and col "F" for example, what changes would i have to make to the (snb )routine.

BTW thanks for time on this most appreciated.

Rob

shrivallabha
08-15-2012, 11:08 PM
With snb around you can expect shorter code. Try:
Sub Test()
Dim varResult
varResult = Filter(Application.Transpose(Evaluate("=IF(T1:T200=""I"",B1:B200&""|""&F1:F200,""~"")")), "~", False)
With Sheet2.Cells(1)
.Resize(UBound(varResult) + 1, 1) = Application.Transpose(varResult)
.CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End With
End Sub

snb
08-16-2012, 02:36 AM
To 'copy' only column B and F Use:




Sub snb()
sn = Filter([transpose(If(Sheet1!T1:T200="I",row(1:200),"~"))], "~", False)
Sheet3.Cells(1).Resize(UBound(sn) + 1, 2) = Application.Index(Sheet1.Cells(1).CurrentRegion.Value, Application.Transpose(sn), array(2,6))
End Sub

Rob342
08-16-2012, 05:37 AM
Shrivallabha
many thanks throws rte (13) code

Mancubus
Thanks for all work and code, which i can use at later date

Snb
Many thanks snb for the code and your time, it works great now to find just the data for a whole row or just 2 columns. Took me some time to decipher the code but this now allows me to extend it as well, to bring in as many columns as required, by altering the array sizes.

Now onto the next step of this multipage form.......
I'll mark this thread as solved, but i could be back for more help

Rob

shrivallabha
08-16-2012, 05:53 AM
Shrivallabha
many thanks throws rte (13) code

:

Rob It gives me different error when I try to run it 2nd time with sheet2 not empty.

Sub Test()
Dim varResult
varResult = Filter(Application.Transpose(Evaluate("=IF(T1:T200=""I"",B1:B200&""|""&F1:F200,""~"")")), "~", False)
With Sheet2.Cells(1)
.Resize(UBound(varResult) + 1, 2).ClearContents
.Resize(UBound(varResult) + 1, 1) = Application.Transpose(varResult)
.CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End With
End Sub

See attached file.

snb
08-16-2012, 07:14 AM
As you may have noticed the Index function provides a means to 'filter' in rows and to 'filter' in columns in an array (or Range) simultaneously.

shrivallabha
08-16-2012, 08:58 AM
As you may have noticed the Index function provides a means to 'filter' in rows and to 'filter' in columns in an array (or Range) simultaneously.
I had tried to search for way to get slice of an array and had come across this application.index option but couldn't make it work satisfactorily. You demo of Filter and Index (here it is little different) is really good. Many thanks.