If Nothing is Found Then Give Alert

[jrnewport1115]

Hello and Happy Friday!

I'm looking to only have an alert display in a msgbox if the quote number I am searching for is not found in the database I'm searching in.
Would love help on this along with any suggestions on how to make this more efficient.

Thanks in advance!
Justin

Sub deletequotefromdatabase()

Dim Quote As Variant

'grab quote number from quote form
Quote = Workbooks("testsource.xlsm").Sheets(1).Range("B1").Value

'search for quote number in the database
With Workbooks.Open("S:\Sales Group\Quotes\TestDatabase.xlsx").Sheets("sheet1")
    .Cells.Find(What:=Quote, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).EntireRow.Delete
End With

'save and close the dataabase
Workbooks("TestDatabase.xlsx").Save
Workbooks.Open("S:\Sales Group\Quotes\TestDatabase.xlsx").Close

End Sub

[cytop]

Just as an aside... What's the reasoning behind:

Workbooks.Open("S:\Sales Group\Quotes\TestDatabase.xlsx").Close

..?

[jrnewport1115]

Hey cytop.....not sure!....I'm very new so I'm just piecemealing other's code to make mine work.

My goal is to open the "database" from the shared drive at the office, search for the quote and delete if found, then save and close the database!

[cytop]

I would make the code more 'inefficient' to start with (You'll never notice any difference)

Sub deletequotefromdatabase()

   Dim Quote As Variant
   Dim r As Excel.Range
   Dim wb As Excel.Workbook
   
   'grab quote number from quote form
   Quote = Workbooks("testsource.xlsm").Sheets(1).Range("B1").Value

   'search for quote number in the database
   Set wb = Workbooks.Open("S:\Sales Group\Quotes\TestDatabase.xlsx")
   
   Set r = wb.Sheets("sheet1").Cells.Find(What:=Quote, After:=ActiveCell, LookIn:=xlFormulas, _
      LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)
    
   If Not r Is Nothing Then
      r.EntireRow.Delete
   Else
      MsgBox "Not found...", vbExclamation
   End If
   
   '// Remainder of code
   
End Sub

Alternatively, you could just do sometihng like

Sub deletequotefromdatabase1()

   Dim Quote As Variant

   On Error GoTo Catch
   
   'grab quote number from quote form
   Quote = Workbooks("testsource.xlsm").Sheets(1).Range("B1").Value

   'search for quote number in the database
   With Workbooks.Open("S:\Sales Group\Quotes\TestDatabase.xlsx").Sheets("sheet1")
       .Cells.Find(What:=Quote, After:=ActiveCell, LookIn:=xlFormulas, _
       LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False).EntireRow.Delete
   End With

   'save and close the dataabase
   Workbooks("TestDatabase.xlsx").Save
   Workbooks.Open("S:\Sales Group\Quotes\TestDatabase.xlsx").Close

   Exit Sub
   
Catch:
   
   MsgBox "Not found..."
   
End Sub

But you'll need to check the error number to make it is not some other error (like unable to open the file...), but the first method would be preferred.

If you actually read the line I mentioned above, you'll see it is opening a workbook only to close it immediately - just seems a little odd.

[jrnewport1115]

Cytop you're awesome. SO appreciate your help.

On that particular line of code:

Workbooks.Open("S:\Sales Group\Quotes\TestDatabase.xlsx").Close

When I remove the ".open" and run the code it gives me a Run Time 91 error telling me the script is out of range.
Any ideas why?

Thanks again!
Justin

[cytop]

If you use the first code sample I gave, then you could simply close 'wb'

   ...
   If Not r Is Nothing Then
      r.EntireRow.Delete
      '// orkbook has now been changed - save it.
      wb.Save
   Else
      MsgBox "Not found...", vbExclamation
   End If
   
   '// Clear the Dirty flag - just in case
   With wb
       .Saved = True
       .Close
   End With

   '// Tidy up
   Set wb = nothing
   Set r = Nothing

End Sub

[jrnewport1115]

Wonderful. Thank you so much.
Final question...partially kidding but somewhat serious:

Why are you guys so kind to answer questions like this on your own time? I am a capitalist and this blows my mind that you all are so willing to do this free of charge.

[cytop]

I'm an altruistic Capitalist with Socialist tendencies...

The other reason is experience - there are issues on boards like this you wouldn't meet in a working lifetime, so it's always good to have a reference point. Of course, those 'interesting' problems only show up every now and then - in the meantime there's all the other issues...

[jrnewport1115]

Haha....like mine....well thanks again..marking as solved...have a great weekend