Find function returning error

**Julesdude** · 08-24-2010, 05:41 AM

Hi there, I'm getting an 'Object Variable or With Block Variable Not Set' error message when my find command runs. This is after a user enters a value like '0092' in my find text box on my user form. Other numbers entered that don't begin with 00 are fine.

Basically what I'm trying to achieve is to have the user type in a number or text in textbox TB9 and in the TB9_change sub, have the find command search a range in one column and return a value in offset (0, 2).

My code is :

If Application.CountIf(Worksheets("Paste Artwork Matrix").Range("G1:G10000"), TB9.Value) > 0 Then
        TB10.Value = Worksheets("Paste Artwork Matrix").Range("G1:G10000").Find(What:=TB9.Value, After:=Range("G1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, 2).Value
        TB10.Visible = True
        TB10Label.Visible = True
End If

Could anyone please advise what's going wrong...and how I can get it to search for cells with 0092 in them?

**contaminated** · 08-24-2010, 05:46 AM

Hi
Try to addset in fron of TB10 ie
Set TB10.Value = Worksheets("Paste Artwork Matrix.....

**Julesdude** · 08-24-2010, 05:51 AM

Unfortunately it still has the same error message

**romperstomper** · 08-24-2010, 06:18 AM

Does your column G data actually have the 00 entered or is it merely a display format?

**Julesdude** · 08-24-2010, 08:38 AM

Hi Romperstomper,

The actual full text in the cell is F0500092 and the format is set to general.

So, in TB9 when the user enters, say, '0092' i need the search in the TB9 change event to reference column G and then find FO500092 and bring back offset 0, 2 to display in TB10.

So yes it does patially contain the 00 you mentioned

**romperstomper** · 08-24-2010, 08:42 AM

Try this:

    Dim rngFound As Range
    With Worksheets("Paste Artwork Matrix")
        Set rngFound = .Range("G1:G10000").Find(What:=tb9.Value, After:=.Range("G1"), LookIn:=xlFormulas, _
                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=False)
    End With
    If rngFound Is Nothing Then
        MsgBox tb9.vlaue & " not found."
    Else
        With TB10
            .Value = rngFound.Offset(0, 2).Value
            .Visible = True
        End With
        TB10Label.Visible = True
    End If

**Julesdude** · 09-10-2010, 10:04 AM

I think it's worked! Many thanks for the code Romperstomper

**romperstomper** · 09-10-2010, 10:45 AM

Glad to help.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save