Adding An If Clause after Prompt
 

What the below macro does is prompt the user for a vendor, find the
vendor in the C column, confirms if this is the correct one, then
deletes that line.

What I'd like it to do is:
- Prompt the user for a vendor
- Find the vendor in C column
- Confirm that is the correct one to delete
- If the B column says monthly, delete 12... if the B column says
yearly, delete 1... if the B column says quarterly, delete 3..

I'm not sure if that is possible but it is ideal.



Sheets("list").Select
Range("C3").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select

Answer = MsgBox("Is this the contract/vendor you would like to
delete?", vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then
Selection.EntireRow.Delete
Exit Sub

Else
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If

Adding An If Clause after Prompt
 

I did not test this, but it should work. Use the Select Case to test the
value in column B.

Sheets("list").Select
Range("C3").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select

Answer = MsgBox("Is this the contract/vendor you would like to
delete?", vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then
Select Case LCAse(Range("B" & Selection.Row).Value)

Case "monthly"
Selection.Resize(12, 1).EntireRow.Delete
Case "quarterly"
Selection.Resize(3, 1).EntireRow.Delete
Case "yearly"
Selection.EntireRow.Delete
End Select
Exit Sub

Else
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If



wrote in message
...
What the below macro does is prompt the user for a vendor, find the
vendor in the C column, confirms if this is the correct one, then
deletes that line.

What I'd like it to do is:
- Prompt the user for a vendor
- Find the vendor in C column
- Confirm that is the correct one to delete
- If the B column says monthly, delete 12... if the B column says
yearly, delete 1... if the B column says quarterly, delete 3..

I'm not sure if that is possible but it is ideal.



Sheets("list").Select
Range("C3").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select

Answer = MsgBox("Is this the contract/vendor you would like to
delete?", vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then
Selection.EntireRow.Delete
Exit Sub

Else
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End If