Excel VBA: Copy a row if cells contain certain data

Question

I am relatively new to coding in general, but here goes:

I have a huge list of membershipdata which I am trying to organize. This is going to be done weekly as the data is variable, so I am trying to automate the work a bit.

My problem is, I want to copy an entire row of data if a specific cell contains a specific text.

I have been able to do so using this code:

Sub OK() Dim c As Range Dim j As Integer Dim Source As Worksheet Dim Target As Worksheet Set Source = ActiveWorkbook.Worksheets("Status") Set Target = ActiveWorkbook.Worksheets("OK") j = 2 For Each c In Source.Range("F1:F300") If c = "Yes" Then Source.Rows(c.Row).Copy Target.Rows(j) j = j + 1 End If Next c End Sub

However, I want to use multiple conditions i.e. I only want the row to be copied if both column E and I contains "Yes".

My initial guess was this, but it doesnt seem right:

For Each c In Source.Range("F1:F300") AND Source.Range("I1:I300")

How can i add a condition to my code? I have tried using "and", but cant get it right it seems.

Thank you in advance.

QHarr · Accepted Answer · 2017-10-26 06:48:05Z

You cannot add another Range in the way you have to a loop. Instead loop the one range as you originally put and as the additional range you want to check matches on a row by row basis but differs in terms of column use OFFSET to test the column I value in the same row. As below:

If c = "Yes" And c.Offset(0, 3) = "Yes"

So all together:

Sub OK() Dim c As Range Dim j As Integer Dim Source As Worksheet Dim Target As Worksheet Set Source = ActiveWorkbook.Worksheets("Status") Set Target = ActiveWorkbook.Worksheets("OK") j = 2 For Each c In Source.Range("F1:F300") If c = "Yes" And c.Offset(0, 3) = "Yes" Then Source.Rows(c.Row).Copy Target.Rows(j) j = j + 1 End If Next c End Sub

Collectives™ on Stack Overflow

Excel VBA: Copy a row if cells contain certain data

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related