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
<tbody>
</tbody>
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
Ac Desc | Record Stat |
CURRENT ACCOUNT - USD (26001) | O - Do not copy |
transit acc USD - (37393) | O - To copy |
TRANSIT ACCOUNT - USD (26030) | O - To copy |
SOCIAL INSURANCE ACC - UAH (26046) | O - Do not copy |
TYPE ACCOUNT - UAH (26003) | C - Do not copy |
CURRENT ACCOUNT - USD (26006) | O - Do not copy |
TRANSIT ACCOUNT - USD (26038) | O - To copy |
TYPE ACCOUNT - UAH (26000) | O - Do not copy |
CURRENT ACCOUNT - USD (26003) | C - Do not copy |
TRANSIT ACCOUNT - USD (26031) | O - To copy |
TRANSIT ACCOUNT - USD (26034) | O - To copy |
TRANSIT FOR PMNTS - UAH(26034) | O - Do not copy |
TRANSIT ACCOUNT - USD (26034) | C - Do not copy |
<tbody>
</tbody>
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