UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Delete Cell With Conditions, Office 2013    
 
   
kenwood1
post Oct 10 2018, 05:26 PM
Post#1



Posts: 489
Joined: 11-July 09



Hi Everyone,

I have three columns a/b/c I would like if column "B" has a text "ARTFITARBT" or "ARTFITT042" and in the same Row column "C" which do not contain a value 121,127 or 633 then delete contain cell B

I have tried, but have no success:

Dim i As Long
' Go through the marks columns
For i = 2 To 10

If Sheet1.Range("B" & i).Value = "ARTFITARBT" Or "ARTFITT042" & Sheet1.Range("C" & i).Value <> 633 Or 121 Or 127 Then

Range("B" & i).ClearContents
End If

Next

Any help is appreciated.


Attached File(s)
Attached File  delete123.png ( 24.18K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Oct 10 2018, 06:44 PM
Post#2


UtterAccess VIP
Posts: 8,971
Joined: 25-October 10
From: Gulf South USA


Hi: See if this works better for you. Note the syntax for multiple tests of the same cell, the use of And instead of & to join two segments and the use of And instead of Or in the last section.

Note that using Or confuses the issue, since, for example, when "<> 633" is true the entire section is true, even if the value is one of the other numbers..

Dim i As Long
' Go through the marks columns
For i = 2 To 10

If (Sheet1.Range("B" & i).Value = "ARTFITARBT" Or Sheet1.Range("B" & i).Value = "ARTFITT042") And (Sheet1.Range("C" & i).Value <> 633 And Sheet1.Range("C" & i).Value <> 121 And Sheet1.Range("C" & i).Value <> 127) Then

Range("B" & i).ClearContents
End If

Next

See if this works for you. It seems to in my test, but I only used limited rows of values.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kenwood1
post Oct 11 2018, 01:58 AM
Post#3



Posts: 489
Joined: 11-July 09



Hi Joe,

Thank you for your help, there is no reaction when I click the button. I put also an attachment, if you have time to have a look.

Thanks again.
Attached File(s)
Attached File  UtterAcess.zip ( 15.3K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Oct 11 2018, 05:35 AM
Post#4


UtterAccess VIP
Posts: 8,971
Joined: 25-October 10
From: Gulf South USA


Hi: For some reason, in your posted spreadsheet (but not in my test file), the code did not like the Sheet1 prefix before .Range. Rather than try to diagnose this further, I activated Sheet1 and then removed that part from the IF logic set. This seems to work now ...

HTH
Joe

CODE
Private Sub CommandButton1_Click()

Worksheets("sheet1").Activate

Dim i As Long
' Go through the marks columns
For i = 2 To 10

If (Range("B" & i).Value = "ARTFITARBT" Or Range("B" & i).Value = "ARTFITT042") And (Range("C" & i).Value <> 633 And Range("C" & i).Value <> 121 And Range("C" & i).Value <> 127) Then

Range("B" & i).ClearContents
End If

Next
End Sub

Attached File(s)
Attached File  UtterAcess_kenwood1_Rev1.zip ( 14.57K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Oct 11 2018, 05:49 AM
Post#5


UtterAccess VIP
Posts: 8,971
Joined: 25-October 10
From: Gulf South USA


Ah. Another approach just dawned on me. You can do away with the Activate if you want and use the fully defined worksheet approach in the IF logic ...

If (Worksheets("sheet1").Range("B" & i).Value = "ARTFITARBT" Or Worksheets("sheet1").Range("B" & i).Value = "ARTFITT042") And (Worksheets("sheet1").Range("C" & i).Value <> 633 And Worksheets("sheet1").Range("C" & i).Value <> 121 And Worksheets("sheet1").Range("C" & i).Value <> 127) Then

This looks like it works. But, for simplicity, you might just go ahead and do the activate, as in the post above, and skip the extra code prefix for .Range.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kenwood1
post Oct 11 2018, 05:57 AM
Post#6



Posts: 489
Joined: 11-July 09



Dear Joe,

Thank you so so so much. It saves alot of my time which I waist already a lot of time.

Again very appreciated notworthy.gif
Go to the top of the page
 
RJD
post Oct 11 2018, 06:06 AM
Post#7


UtterAccess VIP
Posts: 8,971
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that works for you. thumbup.gif

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th October 2018 - 12:49 AM