Simple ClearContents not working

ColinCoady

New Member
Joined
Mar 21, 2019
Messages
35
I can't figure out why this won't work. Can someone take a look?

Code:
Private Sub ClearContents()
Worksheets("Costing").Activate
If Cells(7, 5).Value = "Other %" Then Cells(7, 7).ClearContents
End Sub
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,878
Office Version
  1. 2013
Platform
  1. Windows
You do understand:

Code:
[LEFT][COLOR=#333333][FONT=monospace]If Cells(7, 5).Value = "Other %" Then Cells(7, 7).ClearContents
[/FONT][/COLOR][/LEFT]

Means if Row(7) column(5) And then clear Row(7) Column(7)

These are not the same.
 

ColinCoady

New Member
Joined
Mar 21, 2019
Messages
35
Yes - I want one cell cleared based on the value in a different cell.
Can you help with this?
-Colin
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,637
Office Version
  1. 365
Platform
  1. Windows
What is the value in E7 on the Costing sheet?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Colin, check that the string in E7 has no leading or trailing spaces, also check that it is only one space between the r and the %.
 

ColinCoady

New Member
Joined
Mar 21, 2019
Messages
35
The value in E7 is from a Data validation list with 5 choices. One of the choices is "Other %"
I changed "Other %" to "Other%" (no space) and changed the code accordingly. Still doesn't want to work.
Still missing something - just not sure what. Any thoughts?
Thanks MARK858 and Fluff
-Colin
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

None from me at the moment as long as it is G7 being cleared.

Just out of interest can you copy and paste (Please copy/paste it, don't retype it) the result that appears in your immediate window if you run the code below.

Code:
Sub testit()
Debug.Print "| &"; Cells(7, "E").Value; "& |", Len(Cells(7, "E").Value)
End Sub
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Then I am afraid I have no ideas at the moment (if you don't have any other code taking effect and no protection, which would have produced an error) as your code is clearing G7 for me.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test following

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$E$7" Then Exit Sub
  If Target = "Other %" Then Target.Offset(0, 2).ClearContents
End Sub

Hope this will help
 

Watch MrExcel Video

Forum statistics

Threads
1,109,486
Messages
5,529,152
Members
409,851
Latest member
Ingar
Top