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
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)
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"
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
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.