Macro for input box to replace formula cells with 0

[dnasim]

Hello I have this worksheet.

Sample Data from "Demand Planning Prem" worksheet

Date	SKU	Name	Begin FG's	Add Returns	Demand FG	End FG's	Weeks FG	Excess FG
6/20/2014	ASDRK-301	Tent	5718	1	58	5661	98	5339
6/27/2014	ASDRK-301	Tent	5661	7	36	5632	156	5310
7/4/2014	ASDRK-301	Tent	5632	4	38	5598	147	5276
7/11/2014	ASDRK-301	Tent	5598	7	38	5560	146	5238
7/18/2014	ASDRK-301	Tent	5560	5	38	5522	145	5200
8/1/2014	ASDRK-301	Tent	5522	2	38	5484	144	5162
8/1/2014	ASDRK-301	Canoe	5484	7	38	5446	143	5124
8/8/2014	ASDRK-301	Canoe	5446	9	38	5408	142	5086

<tbody>
</tbody>

This worksheet has around 40,000 records, and I need to create a Macro that will ask the user for a date, and when the user enters a date the macro goes through column A and finds all of the matching dates and then replaces the numbers (along with the formulas) in the corresponding Add Returns cells (column E) with 0's.

I have only been able to create an inputbox, but I am completely lost regarding the criteria due to being very new to VBA.

Sub ClearData()
Dim EnterDate As Date
Dim msg As String
msg = "Please enter a date in the mm/dd/yyyy format"
EnterDate = Inputbox(msg)
ActiveSheet.Range("A")

If EnterDate = Range("A") Then
Range("E") = 0
End if
End Sub

 

[sastoka]

Hi There


Try this

Sub ClearData()
Dim EnterDate As Date
Dim msg As String
Dim i As Range

Lrow = Sheets("[COLOR=#00ff00]Sheet1[/COLOR]").Cells(Rows.Count, "A").End(xlUp).Row
msg = "Please enter a date in the mm/dd/yyyy format"
EnterDate = InputBox(msg)

For Each i In Range("A1:A" & Lrow)

If EnterDate = i.Value Then
i.Offset(0, 4).Value = 0
End If

Next i

End Sub

Don't forget to modify

"Sheet1"

to reflect the name of your sheet. If you want it to run in the selected shhet change

Sheets("sheet1")

for

ActiveSheet

try this code in a copy of your workbook first

 

[dnasim]

Thank you it worked perfectly!