Macro for input box to replace formula cells with 0

[dnasim]

Hello I have this worksheet.

Sample Data from "Demand Planning Prem" worksheet

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

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!