I have cells that use code to enter data (running total in same cell) and
other cells with formulas that use the data from the code cells. When I clear
the cells both these types of cells they all "0" out. I want completely
different cells to maintain some of these totals but because they clear the
final total cells also go to "0" because there is no value in the cells that
were cleared. I need to add to the clear contents macro so these other cells
keep there totals after the others have cleared.
Please help.
Thanks

There are two methods you should look into. both can be done either by
formulas or VBA


1) special Cells method in VBA. this is equivalent to the following
menu item on a worksheet

Edit - GoTo - special


You can use this method to find cells with and without formulas.


2) PasteSpecial - If you have formulas that you want to retain a value
after the cell that the formula references you must replace the formula
with a value. Use Paste Special and Past the Value only to remove the
formula. the Pastespeciual on the worksheet is only enabled after you
copy cell(s) and is on the Edit Menu.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

Thanks..maybe I was not clear. below is the code I am using with a form
control.

Sub Clear_all()
Range("D6:G6,A7,A11").ClearContents
Range("D10:G10").ClearContents

End Sub

Through functions I record the various totals in columns "M,N,O,and P" which
are dependent on the values in the other cells. When I clear the above cells
with the code there is no value left and so the columns also clear. I would
like to know the code so that the columns retain there values.

I am new at "code" so please do not asume I know what I am doing...I got my
code off the net.

Thank you.

"joel" wrote:


There are two methods you should look into. both can be done either by
formulas or VBA


1) special Cells method in VBA. this is equivalent to the following
menu item on a worksheet

Edit - GoTo - special


You can use this method to find cells with and without formulas.


2) PasteSpecial - If you have formulas that you want to retain a value
after the cell that the formula references you must replace the formula
with a value. Use Paste Special and Past the Value only to remove the
formula. the Pastespeciual on the worksheet is only enabled after you
copy cell(s) and is on the Edit Menu.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

.

This is a little complicated. Let me know if you need futher
explanation


Sub Clear_all()

Set ClearRange = Range("D6:G6,A7,A11,D10:G10")

Set FormulaRange = ClearRange.SpecialCells(xlCellTypeFormulas)

For Each Cell In FormulaRange
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValues
Next Cell

For Each Cell In ClearRange
If Application.Intersect(Cell, FormulaRange) Is Nothing Then
Cell.ClearContents
End If
Next Cell

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

Hi Joel'
Thanks for the reply.
I tried this a couple of ways with the form control and just by running the
Macro and both ways I got a message with a big red "X" and the # 400. Nothing
was cleared. Also I was not sure about the clear syntax as I need to use
clear contents...
Would appreciate any other help you might have.

"joel" wrote:


This is a little complicated. Let me know if you need futher
explanation


Sub Clear_all()

Set ClearRange = Range("D6:G6,A7,A11,D10:G10")

Set FormulaRange = ClearRange.SpecialCells(xlCellTypeFormulas)

For Each Cell In FormulaRange
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValues
Next Cell

For Each Cell In ClearRange
If Application.Intersect(Cell, FormulaRange) Is Nothing Then
Cell.ClearContents
End If
Next Cell

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

.

I need some additional information to understand what you are doing.

First there are two different types of forms. Some people call a
worksheet a form when the format the sheet to take user inputs. Then
there are the VBA Userforms where people add controls. One of the
controls is a user worksheet. It sounds like you have a userform with a
worksheet control that you are trying to clear. I suspect the problem
is you are not refereing to the worksheet control properly. I think I
need to see your code and also I need to know where your code is
located.


If you have a Userform and you are trying to refer to an obect on the
userform you need to do something like this

Userform1.Listbox1.text


Now if you are trying to access an obect on the Userform and you are
trying to get an object on the userform you can use "ME" instead of
specifying Userform1

Userform1.Listbox1.text

or

me.Listbox1.text



Now if you are running code insdie a userform and need to access the
worksheet you need to specfy the worksheet. Activesheet usually won't
work


Sheets("sheet1").Range("A1")


A lot of problems occur because people don't specify the complete
location of obects. I don't leavve anything to chance. I always have
unique names for my worksheets. I also change the default Listbox names
and other objects to the function name. Instead of using Listbox1 I
will change the Listbox name to LastNamebox. I will change userform1 to
NameAdressForm.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat