2nd Input for Row Deletion Requirements not Applying Properly

[Neild137]

I had a code to prompt for two different inputs to search another sheet and the delete the entire row if two conditions where met (entry ID $ and date). I adjusted this to re-select the data worksheet, because it was originally also deleting the actual row of the worksheet that this button was located in.
I think the Date Input is no longer applying properly now. Anybody have any ideas?

<code>Sub DeleteRowNow()

On Error Resume Next

Dim userinput As String, DateInput As String
userinput = InputBox("Enter a value to search for.", "Column A Search")
DateInput = InputBox("Enter a date to search for.", "Column AN Search")
Dim findrange As Range
Dim firstaddress As String
Set findrange = ThisWorkbook.Sheets("Location File Data").Columns("A").Find(what:=userinput, lookat:=xlWhole, LookIn:=xlValues)
If findrange Is Nothing Then
MsgBox "No matching search results"
Exit Sub
Else
Sheets("Location File Data").Select
findrange.EntireRow.Select
Selection.EntireRow.Delete
Sheets("Compare").Select
End If

End Sub</code>

 

[Scott T]

The only time Dateimput is in your code is where it is declared and where it is assigned a value. It is never used as a condition.

Sub DeleteRowNow()

On Error Resume Next

Dim userinput As String, [COLOR=#ff0000]DateInput As String[/COLOR]
userinput = InputBox("Enter a value to search for.", "Column A Search")
[COLOR=#ff0000]DateInput = InputBox("Enter a date to search for.", "Column AN Search")[/COLOR]
Dim findrange As Range
Dim firstaddress As String
Set findrange = ThisWorkbook.Sheets("Location File Data").Columns("A").Find(what:=userinput, lookat:=xlWhole, LookIn:=xlValues)
If findrange Is Nothing Then
MsgBox "No matching search results"
Exit Sub
Else
Sheets("Location File Data").Select
findrange.EntireRow.Select
Selection.EntireRow.Delete
Sheets("Compare").Select
End If

End Sub

 

[Neild137]

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim userinput As String, DateInput As String
userinput = InputBox("Enter a value to search for.", "Column A Search")
DateInput = InputBox("Enter a date to search for.", "Column AN Search")
Dim findrange As Range
Dim firstaddress As String
Set findrange = ThisWorkbook.Sheets("Location File Data").Columns("A").Find(what:=userinput, lookat:=xlWhole, LookIn:=xlValues)
If findrange Is Nothing Then
MsgBox "No matching search results"
Else
firstaddress = findrange.Address
Do
    If DateValue(DateInput) = Sheets("Location File Data").Cells(findrange.Row, "AN").Value Then
    Sheets("Location File Data").Select
    findrange.EntireRow.Select
    Selection.EntireRow.Delete
    Sheets("Compare").Select
        ThisWorkbook.Sheets("Location File Data").Range("A" & lastrowsheet2, "AN" & lastrowsheet2).Value = ThisWorkbook.Sheets("Sheet1").Range("A" & findrange.Row, "AN" & findrange.Row).Value
    End If
    Set findrange = ThisWorkbook.Sheets("Location File Data").Columns("A").FindNext(findrange)
    ' Loop until the Find has wrapped back around, or value not found any more
Loop While Not findrange Is Nothing And findrange.Address <> firstaddress
End If
Application.ScreenUpdating = True

End Sub

 

[Scott T]

Is this code working?

 

[Neild137]

No, I'm still having the same problem

 

[Scott T]

Are you getting an error or is it just not giving you the desired results?

 

[Neild137]

I am getting a catastrophic error on the following line:

Set findrange = ThisWorkbook.Sheets("Restated Data").Columns("A").Find(what:=userinput, lookat:=xlWhole, LookIn:=xlValues)

 

[Scott T]

I do not get an error on that line

I do get an error on this line Run-time Error '424': Object Required. Since you have deleted the row the findrange has been cleared. Also your code does not assign a value to lastrowsheet2

ThisWorkbook.Sheets("Location File Data").Range("A" & lastrowsheet2, "AN" & lastrowsheet2).Value = ThisWorkbook.Sheets("Sheet1").Range("A" & findrange.Row, "AN" & findrange.Row).Value

 

[Scott T]

Also you are setting the calculation to manual but not changing it back to automatic. I assume you want to change it to automatic.

 

[Scott T]

This worked for me

Sub DeleteRowNow()
Dim userinput As String, DateInput As String
userinput = InputBox("Enter a value to search for.", "Column A Search")
DateInput = InputBox("Enter a date to search for.", "Column AN Search")
Dim lr As Long
lr = Worksheets("Location File Data").Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
    If UCase(Worksheets("Location File Data").Cells(i, 1)) = UCase(userinput) And Worksheets("Location File Data").Cells(i, 40) = DateValue(DateInput) Then
        Rows(i).EntireRow.Delete
    End If
Next i
End Sub