VBA To Delete Values Without Deleting Formulas

[VinceF]

Thank you in advance for any assistance.
Novice user, Office 2016, Win 10
With using a command button, I'm looking for a VBA code that would clear only the values in particular cells and ranges without deleting the formulas. I have managed to learn how to replace text in a cell but I am unable to figure out how to clear values while maintaining formulas where needed. If you could post a string that would do this and where I would add it into the existing code that would be very helpful.

Thank You,
VinceF


Private Sub CommandButton1_Click()

Dim warning
warning = MsgBox(Range("A1").Value & " WARNING...!!!! This will reset the entire sheet. Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "Warning")
If warning = vbCancel Then Exit Sub

Sheets("main").Range("E4").Value = "SKINS ENTRY FEE"

MsgBox "THE FORM HAS BEEN RESET."

End Sub

 

[Fluff]

What cells do you want to clear?

 

[VinceF]

What cells do you want to clear?

Fluff, There are numerous cells and some ranges that I will need to clear. I'm going to attempt to post the spreadsheet via XL2BB.

Golf Skins Stableford MASTER TEMPLATE 2-14-22DeletsFormulaNeedToFix.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
2	TEAM GEN	TEAMS	HALF SKINS	FULL SKINS	QUOTA PTS	DATA		TEE		RATING		SLOPE		COR FAC		MAIN						PLAYING CONDITIONS						BUNKERS
3
4	GAME TYPE			SKINS ENTRY FEE	STABLEFORD ENTRY FEE			SELECT TEE		RATING		SLOPE		CR-PAR		SELECT COURSE						PLAYING CONDITIONS						BUNKER RELIEF
5
6	Stroke Adj.			# Skin Players	Skin Pool			HOLE	1	2	3	4	5	6	7	8	9	IN	10	11	12	13	14	15	16	17	18	OUT	TOTAL
7
8	Skins Front		Skins Back	Total Skins	Skin Value
9
10
11	Team Req	Player	Index	HDCP	Strokes	Quota		Skins	F R O N T										B A C K										Totals	Pts
12
13
14
15
16
17
Main

Cell Formulas
Range	Formula
K4	K4	=IF($I$4="select tee","RATING",IF(I4="blue",'Data Sheet'!$B$5,IF(I4="blue/wh",'Data Sheet'!$B$6,IF(I4="white",'Data Sheet'!$B$7,IF($I4="Red/Wh",'Data Sheet'!$B$8,IF(I4="red",'Data Sheet'!$B$9))))))
L4	L4	=IF(#REF!="blue",'Data Sheet'!$B$5,IF(#REF!="blue/wh",'Data Sheet'!$B$6,IF(#REF!="white",'Data Sheet'!$B$7,IF(#REF!="Red/Wh",'Data Sheet'!$B$8,IF(#REF!="red",'Data Sheet'!$B$9)))))
M4	M4	=IF(I4="select tee","SLOPE",IF(I4="blue",'Data Sheet'!$C$5,IF(I4="blue/wh",'Data Sheet'!$C$6,IF(I4="white",'Data Sheet'!$C$7,IF(I4="Red/Wh",'Data Sheet'!$C$8,IF(I4="red",'Data Sheet'!$C$9))))))
N4	N4	=IF(#REF!="blue",'Data Sheet'!$C$5,IF(#REF!="blue/wh",'Data Sheet'!$C$6,IF(#REF!="white",'Data Sheet'!$C$7,IF(#REF!="Red/Wh",'Data Sheet'!$C$8,IF(#REF!="red",'Data Sheet'!$C$9)))))
O4	O4	=IF(I4="select tee","CR-PAR",IF(I4="blue",'Data Sheet'!$D$5,IF(I4="blue/wh",'Data Sheet'!$D$6,IF(I4="white",'Data Sheet'!$D$7,IF(I4="Red/Wh",'Data Sheet'!$D$8,IF(I4="red",'Data Sheet'!$D$9))))))
E7	E7	=IF($E$4="0","",IF($B$4="stableford","",COUNTIF(C12:C51,"*")))
F7	F7	=IF(B4="game type","",IF(B4="stableford","",IFERROR(E7*E4,"")))
I7	I7	=IF($I$4="select tee","","Yards")
T7:AB8,J7:R8	J7	=IF($I$4="select tee","",IF($I$4="blue",'Data Sheet'!C19,IF($I$4="BLUE/WH",'Data Sheet'!C22,IF($I$4="WHITE",'Data Sheet'!C25,IF($I$4="RED/WH",'Data Sheet'!C28,IF($I$4="RED",'Data Sheet'!C31))))))
S7,AC7	S7	=IF($I$4="select tee","",SUM(J7:R7))
AD7	AD7	=IF(I4="select tee","",SUM(S7+AC7))
I8	I8	=IF($I$4="select tee","","HDCP")
I9	I9	=IF($I$4="select tee","","Par")
T9:AB9,J9:R9	J9	=IF($I$4="select tee","",'Data Sheet'!C$21)
S9	S9	=IF(I4="select tee","",SUM(J9:R9))
AC9	AC9	=IF(I4="select tee","",SUM(T9:AB9))
AD9	AD9	=IF(I4="select tee","",SUM(S9+AC9))
B9,D9	B9	=IFERROR('Data Sheet'!B76,"")
E9	E9	=IFERROR(B9+D9,"")
F9	F9	=IF(E9="","",IF(B4="game type","",IF(B4="stableford","",'Data Sheet'!S14)))
E12:E17	E12	=IF($D12="","",SUM(ROUNDUP($D12*$M$4/113+$O$4,0)))
F12:F17	F12	=IF($C12="","",IF($B$4="stableford","",$E12))
G12:G17	G12	=IF($B$4="skins","",IF($D12="","",ROUND(IF($D12="","",35-$E12),0)))
AC12:AC17	AC12	=IF(D12="","",SUM(T12:AB12))
AD12:AD17	AD12	=IF(D12="","",SUM(S12+AC12))
AE12:AE17	AE12	=IF($B$4="skins","",IF($D12="","",'Quota Points'!$W6))
S12:S17	S12	=IF(D12="","",SUM(J12:R12))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I11	Expression	=($B$4="skins")	text	NO
C11	Expression	=ISNUMBER($F$4)	text	NO
I11	Expression	=($B$4="stableford")	text	NO
B6:C6	Expression	=ISTEXT($B$7)	text	NO
C11	Expression	="if(and($B$2=""skins"",$E$2=ismumber)"	text	NO
B6:C6	Expression	=ISNUMBER(E4)	text	NO
C11	Expression	=ISTEXT($C$12)	text	NO
B6:C6	Expression	=$B$4="stableford"	text	NO
I11	Expression	=ISTEXT($I$12:$I$51)	text	NO
I11	Expression	=ISNUMBER($D$12:$D$51)	text	NO
I11	Expression	=$B$4="Stableford"	text	YES
C11	Expression	=ISTEXT($B$7)	text	NO
D11	Expression	=ISNUMBER($D$12)	text	NO
D11	Expression	=ISTEXT($C$12)	text	NO
B6:C6	Expression	=$AG$4="Point"	text	NO
E4	Expression	=ISNUMBER($F$4)	text	NO
F4:G4	Expression	=$B$4="skins"	text	NO
F4:G4	Expression	=ISNUMBER($F$4)	text	NO
F4:G4	Expression	=$B$4="skins & stableford"	text	NO
F4:G4	Expression	=$B$4="stableford"	text	NO
F4:G4	Expression	=$B$4="skins"	text	NO
E4	Expression	=ISNUMBER($E$4)	text	NO
E4	Expression	=$B$4="skins & stableford"	text	NO
E4	Expression	=$B$4="stableford"	text	NO
E4	Expression	=$B$4="skins"	text	NO
B4:D4	Expression	=$B$4="skins & stableford"	text	NO
B4:D4	Expression	=$B$4="stableford"	text	NO
B4:D4	Expression	=$B$4="skins"	text	NO
B4:D4	Expression	=$AC$4="no"	text	NO
B4:D4	Expression	=$AC$4="lift rake & place"	text	NO
AC4:AE4	Expression	=$AC$4="no"	text	NO
AC4:AE4	Expression	=$AC$4="lift rake & Place"	text	NO
AC4:AE4	Expression	=$W$4="lift - clean - place fairway only"	text	NO
AC4:AE4	Expression	=$W$4="lift - clean - place"	text	NO
AC4:AE4	Expression	=$W$4="playing ball down"	text	NO
W4:AB4	Expression	=$W$4="Lift - Clean - Place Fairway Only"	text	NO
W4:AB4	Expression	=$W$4="lift - clean - place"	text	NO
W4:AB4	Expression	=$W$4="playing ball down"	text	NO
W4:AB4	Expression	=$I$4="Red"	text	NO
W4:AB4	Expression	=$I$4="Red/Wh"	text	NO
W4:AB4	Expression	=$I$4="White"	text	NO
W4:AB4	Expression	=$I$4="Blue/Wh"	text	NO
I4:J4	Expression	=$I$4="Red"	text	NO
I4:J4	Expression	=$I$4="Red/Wh"	text	NO
I4:J4	Expression	=$I$4="white"	text	NO
I4:J4	Expression	=$I$4="Blue/Wh"	text	NO
I4:J4	Expression	=$I$4="Blue"	text	NO
I4:J4	Expression	=$Q$4="paint creek country club"	text	NO
Q4:V4	Expression	=$Q$4="paint creek country club"	text	NO
W4:AB4	Expression	=$I$4="Blue"	text	NO
F9:G9	Expression	=ISERROR(F9)	text	NO
H13:H51	Cell Value	contains "No"	text	NO
H12	Cell Value	contains "No"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I4:J4	List	='Data Sheet'!$A$4:$A$9
Q4:V4	List	='Data Sheet'!$X$2:$X$6
W4:AB4	List	='Data Sheet'!$N$35:$N$38
AC4:AE4	List	='Data Sheet'!$K$12:$K$14
I12:I17	List	='Data Sheet'!$M$11:$M$13
B12:B17	List	='Data Sheet'!$M$11:$M$13
B7:C7	List	='Data Sheet'!$J$36:$J$38
B4:D4	List	='Data Sheet'!$F$3:$F$6
E4	List	='Data Sheet'!$Y$6:$Y$26
F4:G4	List	='Data Sheet'!$AA$2:$AA$22

 

[Fluff]

That doesn't really answer my question. I have no idea which of those cells you want to clear.

 

[VinceF]

This is a spreadsheet that many of the cells get populated by either direct input, dropdown cells or based on input from other cells. I would need to clear values in specific cells, ranges of cells, etc..
Some examples would be, B12:B17, C12:C17, D12:D17 are all direct input. C4, E4 and F4 are populated by a drop down box and the text that's in there now would need to be replaced when reset (I figured out how to do that). J12:R12, J13:R13, etc. are populated by direct input. With very, very limited knowledge of VBA would there not be a string to add in the code that would delete the value of the cell while preserving the formula? Everything I have so far, I did by googling and trial and error.
I hope this helps.
Thank You

VinceF

 

[Fluff]

This will clear all cells that do not contain a formula from row12 to 17

   Range("12:17").SpecialCells(xlConstants).ClearContents

 

[VinceF]

This will clear all cells that do not contain a formula from row12 to 17

   Range("12:17").SpecialCells(xlConstants).ClearContents

Thank You Fluff...it appears to be working as needed (sorry for my limited skills and my inability to express myself adequately)

 

[Fluff]

You're welcome & thanks for the feedback.