I was wondering if someone could help me with a macro where it would remove
cell contents if certain criteria is not met.
Weekly i create a new file to be distributed that I need to clean up up cell
contents that does not meet specific criteria.
The criteria I need to meet is the following, column name listed first, than
criteria, for all others I will need the cell contents removed for the column
name listed below.

Column Name and Criteria where cell contents are needed for three columns
within worksheet:
Year = 2008 or greater
Defect = "Yes"
Human Error = "Yes or "No"
Status = "Closed", or" Closed - No Action" or "Contionous"

If this criteria is not met above than remove cell contents in the following
columns: "Project", "Details", and "Cost"
I am thinking I need some type of dowhile loop, but not real fluent on VBA.
Any help would be greatly appreciated.

Some clarification please...

1) First off, what columns (letters) are those names in?

2) Out of curiosity, is there any other answers besides "Yes" or "No" for
the "Human Error" column?

3) When you say "If this criteria is not met"... does that mean if ANY ONE
column does not meet the condition you posted (even if the other 3 do), that
is enough to warrant clearing out the other columns you listed?

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
I was wondering if someone could help me with a macro where it would remove
cell contents if certain criteria is not met.
Weekly i create a new file to be distributed that I need to clean up up
cell
contents that does not meet specific criteria.
The criteria I need to meet is the following, column name listed first,
than
criteria, for all others I will need the cell contents removed for the
column
name listed below.

Column Name and Criteria where cell contents are needed for three columns
within worksheet:
Year = 2008 or greater
Defect = "Yes"
Human Error = "Yes or "No"
Status = "Closed", or" Closed - No Action" or "Contionous"

If this criteria is not met above than remove cell contents in the
following
columns: "Project", "Details", and "Cost"
I am thinking I need some type of dowhile loop, but not real fluent on
VBA.
Any help would be greatly appreciated.

1)It can vary from report to report but lets say the first four columns then
I can tweak the macro if needed, if possible


2) Yes, there can be "Unknown" or a blank cell

3) All Criteria is met

"Rick Rothstein" wrote:

Some clarification please...

1) First off, what columns (letters) are those names in?

2) Out of curiosity, is there any other answers besides "Yes" or "No" for
the "Human Error" column?

3) When you say "If this criteria is not met"... does that mean if ANY ONE
column does not meet the condition you posted (even if the other 3 do), that
is enough to warrant clearing out the other columns you listed?

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
I was wondering if someone could help me with a macro where it would remove
cell contents if certain criteria is not met.
Weekly i create a new file to be distributed that I need to clean up up
cell
contents that does not meet specific criteria.
The criteria I need to meet is the following, column name listed first,
than
criteria, for all others I will need the cell contents removed for the
column
name listed below.

Column Name and Criteria where cell contents are needed for three columns
within worksheet:
Year = 2008 or greater
Defect = "Yes"
Human Error = "Yes or "No"
Status = "Closed", or" Closed - No Action" or "Contionous"

If this criteria is not met above than remove cell contents in the
following
columns: "Project", "Details", and "Cost"
I am thinking I need some type of dowhile loop, but not real fluent on
VBA.
Any help would be greatly appreciated.

Okay, I assumed the following...

Column A: Year (as a 4-digit number)
Column B: Defect
Column C: Human Error
Column D: Status
Column E: Project
Column F: Details
Column G: Cost

If I understand your "All Criteria is met" answer correctly, here is the
macro to do what you want...

Sub TestAndClear()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
If Not (.Cells(X, "A").Value = 2008 And .Cells(X, "B") = "Yes" _
And InStr("*Yes*No*", "*" & .Cells(X, "C") & "*") 0 _
And InStr("*Closed*Closed - No Action*Contionous*", _
"*" & .Cells(X, "D") & "*") 0) Then
.Cells(X, "E").ClearContents
.Cells(X, "F").ClearContents
.Cells(X, "G").ClearContents
End If
Next
End Sub

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
1)It can vary from report to report but lets say the first four columns
then
I can tweak the macro if needed, if possible


2) Yes, there can be "Unknown" or a blank cell

3) All Criteria is met

"Rick Rothstein" wrote:

Some clarification please...

1) First off, what columns (letters) are those names in?

2) Out of curiosity, is there any other answers besides "Yes" or "No" for
the "Human Error" column?

3) When you say "If this criteria is not met"... does that mean if ANY
ONE
column does not meet the condition you posted (even if the other 3 do),
that
is enough to warrant clearing out the other columns you listed?

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
I was wondering if someone could help me with a macro where it would
remove
cell contents if certain criteria is not met.
Weekly i create a new file to be distributed that I need to clean up up
cell
contents that does not meet specific criteria.
The criteria I need to meet is the following, column name listed first,
than
criteria, for all others I will need the cell contents removed for the
column
name listed below.

Column Name and Criteria where cell contents are needed for three
columns
within worksheet:
Year = 2008 or greater
Defect = "Yes"
Human Error = "Yes or "No"
Status = "Closed", or" Closed - No Action" or "Contionous"

If this criteria is not met above than remove cell contents in the
following
columns: "Project", "Details", and "Cost"
I am thinking I need some type of dowhile loop, but not real fluent on
VBA.
Any help would be greatly appreciated.

I am receving a Compile Error: Expected End With

"Rick Rothstein" wrote:

Okay, I assumed the following...

Column A: Year (as a 4-digit number)
Column B: Defect
Column C: Human Error
Column D: Status
Column E: Project
Column F: Details
Column G: Cost

If I understand your "All Criteria is met" answer correctly, here is the
macro to do what you want...

Sub TestAndClear()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
If Not (.Cells(X, "A").Value = 2008 And .Cells(X, "B") = "Yes" _
And InStr("*Yes*No*", "*" & .Cells(X, "C") & "*") 0 _
And InStr("*Closed*Closed - No Action*Contionous*", _
"*" & .Cells(X, "D") & "*") 0) Then
.Cells(X, "E").ClearContents
.Cells(X, "F").ClearContents
.Cells(X, "G").ClearContents
End If
Next
End Sub

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
1)It can vary from report to report but lets say the first four columns
then
I can tweak the macro if needed, if possible


2) Yes, there can be "Unknown" or a blank cell

3) All Criteria is met

"Rick Rothstein" wrote:

Some clarification please...

1) First off, what columns (letters) are those names in?

2) Out of curiosity, is there any other answers besides "Yes" or "No" for
the "Human Error" column?

3) When you say "If this criteria is not met"... does that mean if ANY
ONE
column does not meet the condition you posted (even if the other 3 do),
that
is enough to warrant clearing out the other columns you listed?

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
I was wondering if someone could help me with a macro where it would
remove
cell contents if certain criteria is not met.
Weekly i create a new file to be distributed that I need to clean up up
cell
contents that does not meet specific criteria.
The criteria I need to meet is the following, column name listed first,
than
criteria, for all others I will need the cell contents removed for the
column
name listed below.

Column Name and Criteria where cell contents are needed for three
columns
within worksheet:
Year = 2008 or greater
Defect = "Yes"
Human Error = "Yes or "No"
Status = "Closed", or" Closed - No Action" or "Contionous"

If this criteria is not met above than remove cell contents in the
following
columns: "Project", "Details", and "Cost"
I am thinking I need some type of dowhile loop, but not real fluent on
VBA.
Any help would be greatly appreciated.

Yep... I left it out. The End With statement should go immediately before the End Sub statement. Sorry.

--
Rick (MVP - Excel)


"Jen_T" wrote in message ...
I am receving a Compile Error: Expected End With

"Rick Rothstein" wrote:

Okay, I assumed the following...

Column A: Year (as a 4-digit number)
Column B: Defect
Column C: Human Error
Column D: Status
Column E: Project
Column F: Details
Column G: Cost

If I understand your "All Criteria is met" answer correctly, here is the
macro to do what you want...

Sub TestAndClear()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
If Not (.Cells(X, "A").Value = 2008 And .Cells(X, "B") = "Yes" _
And InStr("*Yes*No*", "*" & .Cells(X, "C") & "*") 0 _
And InStr("*Closed*Closed - No Action*Contionous*", _
"*" & .Cells(X, "D") & "*") 0) Then
.Cells(X, "E").ClearContents
.Cells(X, "F").ClearContents
.Cells(X, "G").ClearContents
End If
Next
End Sub

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
1)It can vary from report to report but lets say the first four columns
then
I can tweak the macro if needed, if possible


2) Yes, there can be "Unknown" or a blank cell

3) All Criteria is met

"Rick Rothstein" wrote:

Some clarification please...

1) First off, what columns (letters) are those names in?

2) Out of curiosity, is there any other answers besides "Yes" or "No" for
the "Human Error" column?

3) When you say "If this criteria is not met"... does that mean if ANY
ONE
column does not meet the condition you posted (even if the other 3 do),
that
is enough to warrant clearing out the other columns you listed?

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
I was wondering if someone could help me with a macro where it would
remove
cell contents if certain criteria is not met.
Weekly i create a new file to be distributed that I need to clean up up
cell
contents that does not meet specific criteria.
The criteria I need to meet is the following, column name listed first,
than
criteria, for all others I will need the cell contents removed for the
column
name listed below.

Column Name and Criteria where cell contents are needed for three
columns
within worksheet:
Year = 2008 or greater
Defect = "Yes"
Human Error = "Yes or "No"
Status = "Closed", or" Closed - No Action" or "Contionous"

If this criteria is not met above than remove cell contents in the
following
columns: "Project", "Details", and "Cost"
I am thinking I need some type of dowhile loop, but not real fluent on
VBA.
Any help would be greatly appreciated.