Inventory workbook issue Search stopped working

[DThib]

Hello all,

I have a complex inventory workbook with a function to search that will populate boxes in a GUI if anything is found and increment to the next record. It was working fine and then I have been making changes for a newer version of Excel and new parameters. Not sure why it is not working.
Here's the code. Can anyone point in a good direction?

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub SearchRule()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim Str As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim c As Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim SrchRng AsRange[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim Answer AsInteger[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Str = Inventory.TB1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'Str = TB1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]     If Str ="" Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      GetFocus[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       ElseIf Str<> "" Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Set SrchRng =Sheets("Inventory").Range("E2",Sheets("Inventory").Range("E:I").End(xlUp))[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Set c =SrchRng.Find(Str, LookIn:=xlValues)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        If Not c IsNothing Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]           FirstAddress = c.Address[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            Do[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                Inventory.TextBox2.Value= c.Offset(0, -3).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TextBox3.Value = c.Offset(0, -2).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TextBox4.Value = c.Offset(0, -1).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TextBox5.Value = c.Offset(0, 1).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                Inventory.TextBox6.Value= c.Offset(0, 0).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TextBox7.Value = c.Offset(0, 5).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TextBox8.Value = c.Offset(0, 6).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TextBox9.Value = c.Offset(0, 9).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                Inventory.TextBox10.Value= c.Offset(0, 2).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TextBox11.Value = c.Offset(0, 3).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Inventory.TB2.Value = c.Offset(0, 4).Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                Answer= MsgBox("Do you want the next Match?", vbYesNoExit)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                Set c= SrchRng.FindNext(c)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                IfAnswer = vbNo Then Exit Do[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]             LoopWhile Not c Is Nothing And c.Address <> FirstAddress[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]              [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]      End If[/COLOR][/SIZE][/FONT]
[FONT="Calibri"][COLOR=#000000] End Sub[/COLOR][/FONT]

Thank you in advance.

DThib

 

[DThib]

Keeps stopping at Str = Inventory.TB1 Message from Debugging (

Str = Inventory.TB1​

******** variable or With block variable not set>)
Seems to be an issue with Inventory.TB1
Spelling is fine.

Running Excel 2010

 

[Fluff]

try

Str=me.TB1

 

[DThib]

Will not let me place in a standard module and does not work in a class module.

 

[Fluff]

Why not run it from the userform module

 

[DThib]

Figured it out. The range is set by the rows at the beginning of the phrase and the last one noted in the formula.
Thanks for looking into this.

Debbie

 

[Fluff]

Glad you got it sorted & thanks for the feedback