Delete Rows using input box to multiple sheets

[Elle_mostlysunny]

Hi,

I was using the below code to delete a row in Sheet 1 and its been working well. However, my dilemma came in when it now needs to run through the same for Sheet2. iSerial is the same, however iRow now is different (column "A:A" from Sheet2. I tried to just insert the same parameters in the below code using Sheet2 information (see fonts in red) but it would only run on one of the sheets not both. Can someone with sublime VBA knowledge save my poor soul and help me? Thank you so much!

**************

Private Sub Delete_Click()

Dim iRow As Long
Dim iSerial As Long

iSerial = Application.InputBox("Please enter Line # to delete.", "Delete", , , , , , 1)

On Error Resume Next

iRow = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(iSerial, Sheets("Sheet 1").Range("U:U"), 0), 0)
iRow = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(iSerial, Sheets("Sheet 2").Range("A:A"), 0), 0)

On Error GoTo 0

If iRow = 0 Then

MsgBox "No record found.", vbOKOnly + vbCritical, "No Record"

Exit Sub

End If

Sheets("Sheet1").Range("AJ1").Value = iRow
Sheets("Sheet1").Range("AK1").Value = iSerial

Sheets("Sheet1").Cells(iRow, 1).EntireRow.Delete shift:=xlUp

Sheets("Sheet2").Range("Y1").Value = iRow
Sheets("Sheet2").Range("Z1").Value = iSerial

Sheets("Sheet2").Cells(iRow, 1).EntireRow.Delete shift:=xlUp

End Sub

 

[RobP]

what if you were to use a different variable for iRow, sheet2, like below ?

Rob

Private Sub Delete_Click()

Dim iRow As Long
Dim iRow2 As Long
Dim iSerial As Long

iSerial = Application.InputBox("Please enter Line # to delete.", "Delete", , , , , , 1)

On Error Resume Next

iRow = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(iSerial, Sheets("Sheet 1").Range("U:U"), 0), 0)
iRow2 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(iSerial, Sheets("Sheet 2").Range("A:A"), 0), 0)

On Error GoTo 0

If AND (iRow = 0, iRow2 = 0) Then

MsgBox "No record found.", vbOKOnly + vbCritical, "No Record"

Exit Sub

End If

Sheets("Sheet1").Range("AJ1").Value = iRow
Sheets("Sheet1").Range("AK1").Value = iSerial

Sheets("Sheet1").Cells(iRow, 1).EntireRow.Delete shift:=xlUp

Sheets("Sheet2").Range("Y1").Value = iRow2
Sheets("Sheet2").Range("Z1").Value = iSerial

Sheets("Sheet2").Cells(iRow2, 1).EntireRow.Delete shift:=xlUp

End Sub

 

[Elle_mostlysunny]

what if you were to use a different variable for iRow, sheet2, like below ?

Rob

Private Sub Delete_Click()

Dim iRow As Long
Dim iRow2 As Long
Dim iSerial As Long

iSerial = Application.InputBox("Please enter Line # to delete.", "Delete", , , , , , 1)

On Error Resume Next

iRow = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(iSerial, Sheets("Sheet 1").Range("U:U"), 0), 0)
iRow2 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(iSerial, Sheets("Sheet 2").Range("A:A"), 0), 0)

On Error GoTo 0

If AND (iRow = 0, iRow2 = 0) Then

MsgBox "No record found.", vbOKOnly + vbCritical, "No Record"

Exit Sub

End If

Sheets("Sheet1").Range("AJ1").Value = iRow
Sheets("Sheet1").Range("AK1").Value = iSerial

Sheets("Sheet1").Cells(iRow, 1).EntireRow.Delete shift:=xlUp

Sheets("Sheet2").Range("Y1").Value = iRow2
Sheets("Sheet2").Range("Z1").Value = iSerial

Sheets("Sheet2").Cells(iRow2, 1).EntireRow.Delete shift:=xlUp

End Sub

I had to stop my dinner when i saw a reply on my thread. You're an angel Rob, I don't know how to thank you. It worked well i just needed to change the IF AND part to 'If iRow = 0 And iRow2 = 0 Then'
Thank you sooooo much!

 

[RobP]

ah sorry, yes, I was still in "formula" mode, not VBA, sorry .. but thanks for feedback.

Glad to be of help
Rob

 

[Elle_mostlysunny]

ah sorry, yes, I was still in "formula" mode, not VBA, sorry .. but thanks for feedback.

Glad to be of help
Rob

No sorry, like i said you saved me, thank you! I just needed to post it for the benefit of people like me who don't know coding. Thank you for your time.