Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a date prompt to get external information | Excel Worksheet Functions | |||
Can I use a between clause or in clause on an IF statement | Excel Programming | |||
IF Clause | Excel Programming | |||
"Between" in an IF clause | Excel Discussion (Misc queries) | |||
Adding "And" clause in SQL string causing SQL Syntax error | Excel Programming |