macro for clearing cells

M

mocc

would it be possible to have a small macro for clearing cells only if a
certain critera is met? ie (if l37 blank, do nothing. if I37 has text, clear
i36,j36,h36 and so on?

thanks
 
J

Jacob Skaria

Try the below

Sub test5()
If WorksheetFunction.IsText(Range("I37")) Then _
Range("H36:J36").ClearContents
End Sub

If this post helps click Yes
 
M

mocc

thanks for this. however i should have said i wanted to insert this into an
exsisting macro. i have tried adding it in but keep getting errors. it workes
great on its own.

JM
 
G

Gord Dibben

Difficult to say why you get errors after inserting.

Post your current macro so's we can see where to insert Jacob's code.


Gord Dibben MS Excel MVP
 
M

mocc

ok here goes.

Sub Macro15()
'
' Macro15 Macro
' Macro recorded 26/04/2008 by jimmoc
'

'
Range("C5:D5").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("C5:D5") = Date
Range("H7:H15").Select
Selection.Copy
Range("E7:E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F7:G15").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("M8:M19").Select
Selection.ClearContents
Selection.ClearContents
Range("B52:M57").Select
Selection.ClearContents
Range("B44:I48").Select
Selection.ClearContents
Range("R47").Select
Selection.Copy
Range("L47:M47").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Range("R51").Select
Application.CutCopyMode = False
Selection.Copy
Range("L51:M51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
'
Range("C5:D5").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("L47:M47").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=3
Range("L51:M51").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=-3
End Sub


thats it. as you can see its for resetting a document so its ready for the
next day.

thanks
 
M

mocc

I37 could have anything in it. I.E text or numbers or time, would this make a
difference?
 
G

Gord Dibben

You can stick the two lines just about anywhere you wish.

Give this a try.

Sub Macro15()

Range("C5:D5") = Date
Range("E7:E15") = Range("H7:H15").Value
Application.CutCopyMode = False
Range("F7:G15,M9:M19,B52:M57,B44:I48").ClearContents

If WorksheetFunction.IsText(Range("I37")) Then _
Range("H36:J36").ClearContents

Range("R47").Copy
Range("L47:M47").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("R51").Copy
Range("L51:M51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Range("C5:D5,L47:M47,L51:M51").Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=3

End Sub


Gord
 
G

Gord Dibben

Yes it would make a difference since Jacob's code looks for Text only.

Use this instead.

If Range("I37") <> "" Then _
Range("H36:J36").ClearContents


Gord
 
M

mocc

thanks you have tidied this up nicely, however with the added code to clear
the cells i get an error 1004??
 
G

Gord Dibben

I don't know why you're getting an error.

All I added was the code for I37

Here is revised code per your request to clear H36:J36 if I37 contains
anything.

Works for me in 2003 and 2007 versions.

Sub Macro15()

Range("C5:D5") = Date
Range("E7:E15") = Range("H7:H15").Value
Application.CutCopyMode = False
Range("F7:G15,M9:M19,B52:M57,B44:I48").ClearContents

If Range("I37") <> "" Then _
Range("H36:J36").ClearContents

Range("R47").Copy
Range("L47:M47").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
Range("R51").Copy
Range("L51:M51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Range("C5:D5,L47:M47,L51:M51").Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=3

End Sub


Gord
 
M

mocc

Thanks for this, it works a treat. i eventually found the problem. some of
the cells were merged and therfore i was not including them in the clearance.

thanks for your time and patience
 
G

Gord Dibben

Don't you just love merged cells<g>

Since xl97 introduced merged cells feature I have seen so many problems with
them that I wonder why they are still included as a feature.

But, good to hear you are sorted out with the macro stuff.

Gord
 
Top