HesterPrynne
New Member
- Joined
- Feb 20, 2017
- Messages
- 42
Hi,
I'm trying to find two values in two different columns. From one column "Ac Desc"(D) I need to copy only rows where met such values among text "2603", "3739" and do not met "UAH". And in anothercolumn "Record Stat" (H) I need only rows with value "O". Macro should copy whole row which met all conditions on a new sheet with keeping formatting.
Thanks in advance!
For example
Column D Column H
[TABLE="width: 412"]
<tbody>[TR]
[TD]Ac Desc[/TD]
[TD]Record Stat[/TD]
[/TR]
[TR]
[TD]CURRENT ACCOUNT - USD (26001)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]transit acc USD - (37393)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26030)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]SOCIAL INSURANCE ACC - UAH (26046)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]TYPE ACCOUNT - UAH (26003)[/TD]
[TD]C - Do not copy[/TD]
[/TR]
[TR]
[TD]CURRENT ACCOUNT - USD (26006)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26038)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TYPE ACCOUNT - UAH (26000)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]CURRENT ACCOUNT - USD (26003)[/TD]
[TD]C - Do not copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26031)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26034)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TRANSIT FOR PMNTS - UAH(26034)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26034)[/TD]
[TD]C - Do not copy[/TD]
[/TR]
</tbody>[/TABLE]
As of now Im using this code , but it can see only one condition:
I'm trying to find two values in two different columns. From one column "Ac Desc"(D) I need to copy only rows where met such values among text "2603", "3739" and do not met "UAH". And in anothercolumn "Record Stat" (H) I need only rows with value "O". Macro should copy whole row which met all conditions on a new sheet with keeping formatting.
Thanks in advance!
For example
Column D Column H
[TABLE="width: 412"]
<tbody>[TR]
[TD]Ac Desc[/TD]
[TD]Record Stat[/TD]
[/TR]
[TR]
[TD]CURRENT ACCOUNT - USD (26001)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]transit acc USD - (37393)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26030)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]SOCIAL INSURANCE ACC - UAH (26046)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]TYPE ACCOUNT - UAH (26003)[/TD]
[TD]C - Do not copy[/TD]
[/TR]
[TR]
[TD]CURRENT ACCOUNT - USD (26006)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26038)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TYPE ACCOUNT - UAH (26000)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]CURRENT ACCOUNT - USD (26003)[/TD]
[TD]C - Do not copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26031)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26034)[/TD]
[TD]O - To copy[/TD]
[/TR]
[TR]
[TD]TRANSIT FOR PMNTS - UAH(26034)[/TD]
[TD]O - Do not copy[/TD]
[/TR]
[TR]
[TD]TRANSIT ACCOUNT - USD (26034)[/TD]
[TD]C - Do not copy[/TD]
[/TR]
</tbody>[/TABLE]
As of now Im using this code , but it can see only one condition:
Code:
Sub FindMe()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
Application.ScreenUpdating = False
If Worksheets("Search Results").AutoFilterMode = True Then Worksheets("Search Results").AutoFilterMode = False
Worksheets("Search Results").Range("B2:H100000").ClearContents
intS = 2
'This step assumes that you have a worksheet named
'Search Results.
Set wSht = Worksheets("Search Results")
strToFind = "2603"
'Change this range to suit your own needs.
With Worksheets("2603").Range("D:D")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
Worksheets("Search Results").Range("H1").Select
Selection.AutoFilter
Worksheets("Search Results").Range("$B$1:$H$22610").AutoFilter Field:=7, Criteria1:="O"
End If
End With
End Sub
