need a macro for a button

A

applebee

New member
Joined
Jun 1, 2011
Messages
15
Reaction score
0
Points
0
hi
Gurus

the forum is awesome

i need a macro to be assigned to a button
the cells must be cleared but not clearing the formulas in the cell

please give me the cod efor the macro to be assigned
thanks
 
As you haven't stated which cells or range...etc this is the best i can do for you
Code: 
Sub Clr_Cells()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
If .HasFormula Then
Else
.ClearContents
End If
End With
End Sub
 
hi please assume

the range from f22:r33 has formulas in it and i need to clear only the cells from h22 to r 33
i am not familiar withthe macro code
so kindly let me know how can i clear the contents within this range which has formulas too

thanks i appreciate your help
 
Simon Lloyd said:
So you want to clear everything in your stated range including formula?
Click to expand...
Simon may be i did not explain properly

the data range starts from F22: R33
and i need to clear the whole data range i.e from F22:R33 when ever i press the button1
without clearing the formulas in it but only the contents

so i have another button2 to bring data which is working perfectly fine


thanks
 
Try this:
Code: 
Sub Clr_Cells()
With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)
If .HasFormula Then
Else
.ClearContents
End If
End With
End Sub
 
Simon Lloyd said:
Try this:
Code: 
Sub Clr_Cells()
With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)
If .HasFormula Then
Else
.ClearContents
End If
End With
End Sub
Click to expand...

it asks me to debug pointing to this line
With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)

i am sorry i am purely not into vb or programming stuff
please advice

thanks


 
one of two things, either the range you are clearing does not have any formula or as you have shown me the line as above it has a space that shouldn't be there between Ty and pe this should read type
 
Simon Lloyd said:
one of two things, either the range you are clearing does not have any formula or as you have shown me the line as above it has a space that shouldn't be there between Ty and pe this should read type
Click to expand...

Helo
Simon

Yes i have formulas and i have mistyped here but not inthe macro creation

Please view my sample file and please advice on the same

all ur helpis appreciated

thanks
 

Attachments

  • test.xlsx
    10.8 KB · Views: 25
Please supply a sample file with the code in place so i can see how you used it, there isn't any code in that workbook!
 
Use this code instead, the previous code faults because you are trying to perform the action on a range that has already been cleared of all values except formulas!
Code: 
Sub Clr_Cells()
On Error GoTo Nxt
With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)
If .HasFormula Then
Else
.ClearContents
End If
End With
Nxt:
End Sub
 
Simon Lloyd said:
Please supply a sample file with the code in place so i can see how you used it, there isn't any code in that workbook!
Click to expand...

Hi
Simon
I have changed none from the code what u have supplied but tried to change my template according to ur code

Code: 
Sub Clr_Cells()
With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)
If .HasFormula Then
Else
.ClearContents
End If
End With
End Sub

thanks
 
Last edited by a moderator:
hi
Simon
please find the scr shot

I am doing exactly the same thing i am sorry i did not understand when u said already cleared of the cells
Nothing like that was carried out
i am attaching the scr shots


thanks
 

Attachments

  • Book5.xlsx
    211.1 KB · Views: 15
Actually you are NOT doing exactly that, you are using my first code sample not the second which is why you are still getting the error, why not post the workbook you are working on?
 
Hi
Simon

I tried to change the range from L46:M46 but it is again giving me the same error i have attached the file for ur reference and i again appreciate ur help in this


thanks
 

Attachments

  • Book4.xlsx
    13.7 KB · Views: 17
What is it you are trying to do??????? that workbook does NOT contain any code. Adding my code to it and running it produced no errors!
 
Simon Lloyd said:
What is it you are trying to do??????? that workbook does NOT contain any code. Adding my code to it and running it produced no errors!
Click to expand...

Simon
as i stated in m ylast post i am jez trying to clear the data , but not the formula using the macro assigned to the button thats all

i dont know why it gives me error withte same code what u have sent me , if i am trying to change the range say l46 :m56 instead of h22:r33

oK LET ME TELL U WHAT IS HAPPEING

the macro what u sent erase the cells from h22:r33

but when i try to change the range from l46:m56

it says cells not found

and going to the debug mode

1.is this true once the cell data is cleared , the formula retained, the cells filled again , still the macro code recognizes it to be cleared cells?

that is all i know about what is happening
2. Also there is another situation here

as u c inthe excel i sent h28 is used in L46 as a formula for example
when i use the macro to clear the data in l46 to m56

then comes the error cell not found though there is data in there bu the good thing is the formulas are not erased
and goes into the debug mode
please advice me
 
Last edited:
Applebee,

Try saving the workbook in an xlsm format, not an xlsx format. When you save it as xlsx all macro code is stripped from the file immediately.
 
Must be something you are doing, i have used your example, added the code, run it many times with no fault, see attached!
 

Attachments

  • WorkingExample.xls
    42.5 KB · Views: 21
You must log in or register to reply here.
Back
Top