Clearing cells but also not clearing other dependent cells

[rtlars]

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

 

[joel]

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.

 

[rtlars]

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]

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

 

[rtlars]

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]

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

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


If you have a Userform and you are trying to refer to an obect on th
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 ar
trying to get an object on the userform you can use "ME" instead o
specifying Userform1

Userform1.Listbox1.text

or

me.Listbox1.text



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


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


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

 

[rtlars]

Joel ...I am not sure that I understand everything you have responded with as
I am new at VBA. I have a control form button on the spreadsheet that has the
following macro:
Sub Clear_all()
Range("D6:G6,A7,A11").ClearContents
Range("D10:G10").ClearContents

End Sub

This is the other code that I also came up with on the net and adjusted:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dblVal As Double, rngInterest As Range, lngCount As Long
On Error GoTo ExitPoint
Application.EnableEvents = False
Set rngInterest = Intersect(Target, Range("A7,D6:G6,A11,D10:G10"))
lngCount = Application.WorksheetFunction.CountA(rngInterest)
If lngCount Then
dblVal = Val(rngInterest(1).Value)
Application.Undo
If lngCount = 1 Then
rngInterest.Value = dblVal + Val(rngInterest.Value)
Else
MsgBox "Multiple Updates Not Permitted", vbCritical, "Computer Says
No"
End If
End If
ExitPoint:
Set rngInterest = Nothing
Application.EnableEvents = True
End Sub

I need to clear contents using the button and clear the contents of the
associated cells but do not clear the cells in columns M,N,O, and P. These
cells have totals equalling the totals in the other cells.
The only other way I can think of is to actually send you a copy of the
spreadsheet as an email attachment...doin't know if that is allowed or
something that is done through this forum.

Thanks again

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/showthread.php?t=185387

Excel Live Chat

.

 

[joel]

I made a change to the 2nd mqcro on the following line. Intersect onl
works if the two ranges are on the same worksheet. Range is usualy th
activesheet but when yo have auserform it sometimes doesn't work if th
focus isn't on a worksheet.

Set rngInterest = Intersect(Target
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))


I added parent. The Parent of a Cell refernce is the sheet, the paren
of the sheet is the workbook. Excel doesn't know which worksheet th
range is refering to and giving an eror. You could also
change Parent to refer to a specific sheet like I made to the 1s
macro.

The 1st macro may fail depending on the object that is selected. I
will fail if you have a sheet that isn't a worksheet. Worksheet are th
spreadsheets that have rows and columns. You can also have sheets lik
charts which aren't worksheets. You will get an error on the clea
macro if the activesheet is a chart. You also get errors sometimes whe
the focus is on an object in the userform.. I added to the 1st macro
rerfernce to either the Activesheet or a specific sheet.

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

or

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



Private Sub Worksheet_Change(ByVal Target As Range)
Dim dblVal As Double, rngInterest As Range, lngCount As Long
On Error GoTo ExitPoint
Application.EnableEvents = False
Set rngInterest = Intersect(Target
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))
lngCount = Application.WorksheetFunction.CountA(rngInterest)
If lngCount Then
dblVal = Val(rngInterest(1).Value)
Application.Undo

If lngCount = 1 Then
rngInterest.Value = dblVal + Val(rngInterest.Value)
Else
MsgBox "Multiple Updates Not Permitted", vbCritical, _
"Computer Says No"
End If
End If

ExitPoint:
Set rngInterest = Nothing
Application.EnableEvents = True
End Sub

 

[rtlars]

Joel ...I was out for a few days. I tried copying your latest efforts but the
macro dialog bos kept poping up and lines five and six were red and I don't
know what that means. Being a novice I have decided to go with multiple
sheets for all the years until I have a bit more knowledge of VBA.
Thanks for all your efforts.

 

[joel]

I think it is just some type error in the line. Make sure your hav
the lines of code exactly like I posted them. I also don't know why bu
sometimes with the Intersect method you need application in front of th
method like below. there are somethings that work a little different o
one PC verses another PC. Never figured out exactly why. I sometime
get things working at home and have to make slight changes when I get t
work. both PC's have windos XP installed and Office 2003.


Set rngInterest = _
Application.Intersect(Target, _
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))


If you are getting two lines highlighted I suspect the line got to
long on the posting and I split the line above using the lin
continuation character (the underline).