Sub ClearButton1_Click()
Range("E5:E12,G3").ClearContents
Range("G5:G12").Formula = _
"=IF(ISBLANK(E5),0,IF(E5=""NM"",0,IF(E5=""CR"",0,G $3)))"
Range("G3").Select
End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

In relation then to the following code, where would you put the line of code
you suggest. I think I am putting it in the wrong area becuase I am getting
a compile error when I try and place it:

Private Sub ClearButton1_Click()

Dim ocell As Range
Range("G3").ClearContents

For Each ocell In Range("E5:E12")
ocell.ClearContents

Range("G5:G12").Formula =
"=IF(ISBLANK(E5),0,IF(E5="NM",0,IF(E5="CR",0,G$3)) )"

Range("G3").Select
Range("G3").Activate

Next ocell
End Sub



"sebastienm" wrote:

One line of code:

Range("G5:G12").Formula =
"=IF(ISBLANK(E5),0,IF(E5="NM",0,IF(E5="CR",0,G$3)) )"

Since E5 is a relative addres (and not $E$5) , excel automatically uses:
- E5 in the formula for cell G5
- E6 in the formula for cell G6
- E7 in the formula for cell G7
- ...
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

This works great, actually both suggestions worked well. What would you say
would be a suggestion to clear the cells I have spoke to and also reset a
additional range of cells to = a formula? Currently, one set of cells has an
if statement in the cell but the use can insert whatever value they need. I
would like my clear button to clear the cells as originally indicated but
also reset cells G5-G12 to =
=IF(ISBLANK(E5),0,IF(E5="NM",0,IF(E5="CR",0,G$3))) on click.

"sebastienm" wrote:

Hi,
Seems like the cells you are trying to clear are already unlocked.
Also, 99% of the time, when you process cells, you do Not need to select
them first.
So your code would be simply:
Range("E5:E12,G3").ClearContents

(no need to re-select or activate any cell since the above code didn't
change the selection)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

In my spreadsheet, I have a clear button (coded below). The whole
sheet (all cells) are locked except range E5 - E12 , G3 and G5
through G12 which are unlocked.

What I want to do upon click of the clear button is for the the
cells in E5-E12 & G3 to be cleared leaving the unprotected cell
range G5 through G12 uncleared then of course leaving all other
locked cells alone. What can I add or change in my code to
accomplish this?


Private Sub ClearButton1_Click()

Dim cell As Range
Range("E5:E12", "G3").Select

For Each cell In Selection
If cell.Locked = False Then
cell.ClearContents
End If

Range("G3").Select
Range("G3").Activate

Next
End Sub