If Statement for Cells.Find

**keyboardqueen** · 05-19-2020, 04:10 PM

Hello,

I have a snippet of code within my macro workbook to search for an item called LibraryKey within a veryhidden sheet. The macro works perfectly fine except for when I'm trying to solve for the IF statement (if LibraryKey isn't found aka a template doesn't exist for the userform request).

Sample workbook with this my specific code in question is attached. I've separated this from the actual workbook (sensitive data) so you won't find some sheets that are referenced or the userform as well. This sub runs as a Private sub within a userform as a heads up.

Goal: When I click the button to run this macro, if the LibraryKey is NOT found in the Library Sheet, then how can I display a message box or a populate a label within the userform that says "Template does not exist in the Library" and to go back to the userform? If the LibraryKey is found then proceed with the subroutine. The attempts I've tried render a Run-time error '91': Object Variable or With block variable not set

Many thanks in advance.
KeyboardQueen

**Keebellah** · 05-19-2020, 04:57 PM

Maybe something like this, since you file is incomplete not able to test

The red text is mine

Worksheets("Library").Activate
Range("A1").Select


'Cells.Find(What:=LibraryKey, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate


Dim foundRange  As Range

With Worksheets("Library").Cells
    Set foundRange = .Find(What:=LibraryKey, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
    If Not foundRange Is Nothing Then
        foundRange.Activate
    Else
        MsgBox LibraryKey & "not found!", vbExclamation, ""
        Exit Sub
    End If
End With
    


Dim ConfigID_Load As String

Range("A2").Select

Do

**keyboardqueen** · 05-22-2020, 12:15 PM

Hello,

Thank you for your efforts! This did not work. I'll have to get some more information shared so that we can do end to end testing with this. I'll probably do this after memorial day weekend when I have a chance.

**Keebellah** · 05-22-2020, 02:15 PM

You can add the file wijt the macro’s and Some dummy data

**Keebellah** · 05-23-2020, 12:53 AM

Could you explain/expand your

This did not work

answer?
It’s the same as phoning your house doctor and tell him/her “ it hurts when I press here” and can’t see you

If Statement for Cells.Find

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If Statement for Cells.Find

Re: If Statement for Cells.Find

Re: If Statement for Cells.Find

Re: If Statement for Cells.Find

Re: If Statement for Cells.Find

Thread Information

Users Browsing this Thread

Similar Threads

IF Statement To Find A Value

[SOLVED] Not Returning Row Number With FIND statement When FIND should be valid

[SOLVED] Find text in cell using cells object with-in IF Then statement

If statement or Find statement?

Find statement help when it doesn't find anything

Have question about using .find statement and brackets around After:= statement

Stopping errors when a find statement doesn't find!

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions