Hello all,

since my last post i can gladly say my macro knownledge is increasing, I am currently working on a spreadsheet that has 5000 rows and I have to copy rows on to a new worksheet if a row has one of 3 criteria, I have written a macro to do 2 of the criteria.

The Criteria is as follows;

AL col contains a "Y"
AN col contains a "Y"

J column contains "dec" or "de'd" or "dedd"

AL and AN only contain "Y" or "N" so the macro was fairly straight forward, im havin issues with Col J since it contains other things i.e. " xdsddxsds dec "

this is the macro i have written

Sub Find()
Set i = Sheets("Sheet1")
Set e = Sheets("Sheet2")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("AL" & j))

If i.Range("AL" & j) = "Y" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

Else
If i.Range("AN" & j) = "Y" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
End If

j = j + 1
Loop
End Sub

But im having major issues in trying to get one macro to to run through all the criteria, any help would be much appriecated.

Hi parmdeep,
It's great to hear that you're picking up VBA well.

I just want to clarify the issue you're having with column J:
Am I right in thining that you want to find a string within a string? So, in your example, that row should be copied because "xdsddxsds dec" contains one of your 3 keywords ("dec" or "de'd" or "dedd")?

If you could just confirm or (if that interpretation is wrong) clarify what you need then we should be able to come up with something for you.

The looping approach you're employing is very logical and a good learning exercise. We might find that there's a better way to achieve your goal though - by using (automating) Excel's built-in Advanced Filter tool.

Hope that helps...

hi Colin,

Yes you are right to assume that it is a string within in a string, im working with a file that col J displays the acc holder's name but if hes deceased (i know gruesome task) it displays Dec, Dec'd, or Deceased I should mention that since its been inputted manually it can be a mix of upper case and lower case characters. i.e. some have written DEC and some Dec and some dec etc.

But i should also mention that some times the acc holder isnt deceased but can still fulfil one of the other criterias. and thus has to be copied.

if i use the filter way will it show for all 3 criteria? (i shall start googling now)

Hi parmdeep,
Effectively then, using the advanced filter, the criteria for that column can just be "dec*", which will pick up any string beginning with the letters "dec" and is not case sensitive. Btw, you could implement some data validation to get the manual entries in that field consistent? A simple boolean flag would seem appropriate (is the person dead, TRUE/FALSE)?


Anyway, the criteria for the advanced filter are:
Column AL is Y
OR
Column AN is Y
OR
Column J is Dec*

This maps to a criteria range as follows:
where the field headers in bold are the column headers in your table. Once that's set up the advanced filter is quite simple to automate. Each critereon is on it's own row which signifies OR conditions.

I've attached an example to show you. The code in the example is simply:
where Range("B13:E1000") is the source table. Of course, the dimensions (number of rows) of the source table can be determined dynamically at runtime. In the example I've added some conditional formatting just to indicate which rows meet the criteria to be shown in the results area.


For learning purposes, if you want me to go over the loop approach you were trying to employ then let me know.


Hope that helps...

Thank you ever so much, from my google searches the troubles i was running into was the i put the whole criteria on the same line i didnt realise for an OR statement they should be on seperate lines. as far incorporating the loop into my macro for this i guess i should stick to using the excel functions for the time being i do not wanna jump in the deep end quite yet enjoyin my armbands! ty again.

Debra Dalgleish has some good material on her site; here's some information on the advanced filter:
Excel Advanced Filter Introduction

You can see on there that your Dec* criterion could be simplified to Dec.

Good luck with the rest of your project!