Loop through a range and copy paste values if conditions are met

[drop05]

Hello i have a sheet ("Sheet 1") that has 4 columns and X amount of rows

column 1 is a list of names
column 2 is a list of IDs
column 3 is positions
column 4 is statues
all starting at row 3 going down
mainly using columns 3 and 4 i am trying to loop through the number of rows, so from D3 to the last row used which could be X, if these conditions are met, as going down the rows if the value in column 4 status is equal to "ACT" and the value of column 3 positions is equal to "High", If both are met then grab the value from column 1, the names, and paste it into a sheet called "Output" starting at row 3 column A so A3 and as the loop is going if another is found then should go in A3 and so on.

example of list

example of output

 

[DanteAmor]

Try this:

Sub Macro2()
  Dim sh2 As Worksheet
  
  Set sh2 = Sheets("Output")
  With Sheets("Sheet 1")
    .Range("F1:G1").Value = .Range("C1:D1").Value
    .Range("F2:G2").Value = Array("High", "ACT")
    sh2.Range("A2").Value = .Range("A1")
    
    .Range("A1:D" & .Range("A" & Rows.Count).End(3)).AdvancedFilter _
        xlFilterCopy, .Range("F1:G2"), sh2.Range("A2")
        
    .Range("F1:G2").Value = ""
    sh2.Range("A2").Value = ""
  End With
End Sub

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[drop05]

Hello thank you for that, i am getting an error here, a runtime error 1004 application defined or object defined error

.Range("A1:D" & .Range("A" & Rows.Count).End(3)).AdvancedFilter _
xlFilterCopy, .Range("F1:G2"), sh2.Range("A2")

also added the excel sheet as recommended
this is the sheet 1 information for example, note that trying to get it if there was more added under it would still loop through

Was also looking at the code and was trying to understand the .range("F1:G1"), is there a reason why range is F and G? Thank you!

Book1.xlsm
	A	B	C	D
1	Name	ID	Position	Status
2
3	John	1234	Inactive	INACT
4	Charlie	4312	Inactive	INACT
5	James	2343	High	ACT
6	Karen	5512	High	ACT
7	Shelly	54343	Low	ACT
8	Morgan	34573	Inactive	INACT
9	Lucie	4344	Low	ACT
10	Nick	34546	High	ACT
11	Rudy	5553	High	ACT
12	Michelle	3412	Low	ACT
13	King	65465	Mid	ACT
14	Damian	7657	Mid	ACT
Sheet 1

Output trying to get it as if conditions are met, and the values put starting from A3 down

Book1.xlsm
	A	B	C
1	Name Output
2
3	James
4	Karen
5	Nick
6	Rudy
7
Output

 

[DanteAmor]

Was also looking at the code and was trying to understand the .range("F1:G1"), is there a reason why range is F and G? Thank you!

The macro uses the advanced filter to perform the required filter, for that I use some cells only as temporary.

Fixed code:

Sub Macro2()
  Dim sh2 As Worksheet
  
  Set sh2 = Sheets("Output")
  With Sheets("Sheet 1")
    .Range("F1:G1").Value = .Range("C1:D1").Value
    .Range("F2:G2").Value = Array("High", "ACT")
    sh2.Range("A2").Value = .Range("A1")
    
    .Range("A1:D" & .Range("A" & Rows.Count).End(3).Row).AdvancedFilter _
        xlFilterCopy, .Range("F1:G2"), sh2.Range("A2")
        
    .Range("F1:G2").Value = ""
    sh2.Range("A2").Value = ""
  End With
End Sub

 

[drop05]

The macro uses the advanced filter to perform the required filter, for that I use some cells only as temporary.

Fixed code:

Sub Macro2()
  Dim sh2 As Worksheet
 
  Set sh2 = Sheets("Output")
  With Sheets("Sheet 1")
    .Range("F1:G1").Value = .Range("C1:D1").Value
    .Range("F2:G2").Value = Array("High", "ACT")
    sh2.Range("A2").Value = .Range("A1")
   
    .Range("A1:D" & .Range("A" & Rows.Count).End(3).Row).AdvancedFilter _
        xlFilterCopy, .Range("F1:G2"), sh2.Range("A2")
       
    .Range("F1:G2").Value = ""
    sh2.Range("A2").Value = ""
  End With
End Sub

Thank you for that, running the code and looking at it, is there a way where its just from the 3rd row down from the values it is grabbing? and then putting them into A2 in the output tab? I notice that it is doing from the first row, i know the first and second row wont have the conditions but the range looking through begin from row 3 to the last row used and only looking at column C and D looking for High in column C and ACT in column D and the ones that have both conditions met grab the value from column A from that row and put it on column A row 2 in output tab and then the next one goes into the following row and so on

 

[DanteAmor]

Try:

Sub Macro3()
  Dim i As Long, j As Long
  j = 2
  With Sheets("Sheet 1")
    For i = 3 To .Range("A" & Rows.Count).End(3).Row
      If .Range("C" & i).Value = "High" And .Range("D" & i).Value = "ACT" Then
        Sheets("Output").Range("A" & j).Value = .Range("A" & i).Value
        j = j + 1
      End If
    Next
  End With
End Sub