Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
Okay it seems to be working, have not completed the run of the macro, a
little slow, could be the amont of data it needs to look at. Also one issue I came across it is removing the column titles for E, F and G. "Rick Rothstein" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
Okay it seems to be working, have not completed the run of the
macro, a little slow VBA code is not noted for its speed when it has to interact with the worksheet. it is removing the column titles for E, F and G Try changing this line... For X = 1 To LastRow to this... For X = 2 To LastRow -- Rick (MVP - Excel) "Jen_T" wrote in message ... Okay it seems to be working, have not completed the run of the macro, a little slow, could be the amont of data it needs to look at. Also one issue I came across it is removing the column titles for E, F and G. "Rick Rothstein" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
It still is removing the column titles, this is what I have for the code. I
tweaked it to which column the data is: Sub TestAndClear() Dim X As Long Dim LastRow As Long With Worksheets("Monthly Export") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow If Not (.Cells(X, "A").Value = 2008 And .Cells(X, "DR") = "Yes" _ And InStr("*Yes*No*", "*" & .Cells(X, "DQ") & "*") 0 _ And InStr("*Closed*Closed - No Action*Continuous*", _ "*" & .Cells(X, "H") & "*") 0) Then .Cells(X, "DS").ClearContents .Cells(X, "DW").ClearContents .Cells(X, "DX").ClearContents End If Next End With End Sub "Rick Rothstein" wrote: Okay it seems to be working, have not completed the run of the macro, a little slow VBA code is not noted for its speed when it has to interact with the worksheet. it is removing the column titles for E, F and G Try changing this line... For X = 1 To LastRow to this... For X = 2 To LastRow -- Rick (MVP - Excel) "Jen_T" wrote in message ... Okay it seems to be working, have not completed the run of the macro, a little slow, could be the amont of data it needs to look at. Also one issue I came across it is removing the column titles for E, F and G. "Rick Rothstein" wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
Rick, it is working fine, I failed to reopen the file and try again once I
updated the 1 to 2 to the last code you sent. Thank you again for all your assistance. Your post was very helpful..... "Jen_T" wrote: It still is removing the column titles, this is what I have for the code. I tweaked it to which column the data is: Sub TestAndClear() Dim X As Long Dim LastRow As Long With Worksheets("Monthly Export") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow If Not (.Cells(X, "A").Value = 2008 And .Cells(X, "DR") = "Yes" _ And InStr("*Yes*No*", "*" & .Cells(X, "DQ") & "*") 0 _ And InStr("*Closed*Closed - No Action*Continuous*", _ "*" & .Cells(X, "H") & "*") 0) Then .Cells(X, "DS").ClearContents .Cells(X, "DW").ClearContents .Cells(X, "DX").ClearContents End If Next End With End Sub "Rick Rothstein" wrote: Okay it seems to be working, have not completed the run of the macro, a little slow VBA code is not noted for its speed when it has to interact with the worksheet. it is removing the column titles for E, F and G Try changing this line... For X = 1 To LastRow to this... For X = 2 To LastRow -- Rick (MVP - Excel) "Jen_T" wrote in message ... Okay it seems to be working, have not completed the run of the macro, a little slow, could be the amont of data it needs to look at. Also one issue I came across it is removing the column titles for E, F and G. "Rick Rothstein" wrote: 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
I was wondering, for experience, can you explain how this code works. Ia m
not understanding the "*". If you could walk through the code. "Rick Rothstein" wrote: Okay it seems to be working, have not completed the run of the macro, a little slow VBA code is not noted for its speed when it has to interact with the worksheet. it is removing the column titles for E, F and G Try changing this line... For X = 1 To LastRow to this... For X = 2 To LastRow -- Rick (MVP - Excel) "Jen_T" wrote in message ... Okay it seems to be working, have not completed the run of the macro, a little slow, could be the amont of data it needs to look at. Also one issue I came across it is removing the column titles for E, F and G. "Rick Rothstein" wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell content removal
The method I used combines all the possible answers into a single string and then uses the InStr function to see if the actual answer is in that string of text somewhere. The asterisks (could actually be any character that does not appear in the string formed by combining all the possible answers) are there to prevent a false hit. Your current situation probably rules out the need to protect against false positive hits now, but may be required if you ever change things. Here is how it works. Consider this combination of possible values... "none", "two", "four". If we just formed the string "nonetwofour" and then used the Instr function to see if an answer of "two" was in there, it would work fine. But what if the user accidentally answered "net"? The Instr function would find that as part of the combined word pars "none" and "net" and report a false hit. So, we divide the words up with a delimiter (I used an asterisk, but as I said earlier, any character that would not appear in any of the possible answers could be used) and use the Instr function to test against this string instead... "*none*two*four*". Okay that stops the false hit at the combination of two joined words, but it does not stop the possible false hit of a word within a word. For example, what if the user answered "one" by mistake. The Instr function would find that word inside "none" and, again, report a false hit. The way to protect against that is not to test the word the user enters, but the word surrounded by asterisks. So, if the user answered "net" or "one", no false hit would be generated because "*net*" or "*one*" would not match any part of "*none*two*four*"... only one of the actual answer words, when surrounded by asterisks, would match any part to the string "*net*two*four*"... hence, no false positives are possible... well, as long as the user doesn't type in "net*two" at least<g. If this last possibility is a concern, an character such as Chr(1) could be used as the delimiter instead of a typeable character (like the asterisks), but this isn't usually something to worry about.
-- Rick (MVP - Excel) "Jen_T" wrote in message ... I was wondering, for experience, can you explain how this code works. Ia m not understanding the "*". If you could walk through the code. "Rick Rothstein" wrote: Okay it seems to be working, have not completed the run of the macro, a little slow VBA code is not noted for its speed when it has to interact with the worksheet. it is removing the column titles for E, F and G Try changing this line... For X = 1 To LastRow to this... For X = 2 To LastRow -- Rick (MVP - Excel) "Jen_T" wrote in message ... Okay it seems to be working, have not completed the run of the macro, a little slow, could be the amont of data it needs to look at. Also one issue I came across it is removing the column titles for E, F and G. "Rick Rothstein" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Content Removal | Excel Programming | |||
Removal of cell contents | Excel Programming | |||
removal of first digit on left in cell | Excel Programming | |||
Removal of cell objects | Excel Discussion (Misc queries) | |||
When content of a cell changes, content of another deletes | Excel Programming |