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

 

[Fluff]

What is the range you want to search?

 

[DThib]

Good morning Fluff,
1worksheet with relevant data from basically column D to Q. It is an incrementing record that will be added to by the user. Once it finds the word or phrase it fills in the textboxes for clarity on the GUI.

Does that help?

Debbie

 

[Fluff]

So the word/phrase can be in any column between D & Q inclusive?

 

[DThib]

I am thinking I will make it 1 column "the Description of Contents (M)".
I think that would be less complicated. The data it will draw from will be from columns D-Q .

So the word/phrase can be in any column between D & Q inclusive?

 

[Fluff]

In that case try

        Set SrchRng = Sheets("Inventory").Range("M2", Sheets("Inventory").Range("M").End(xlUp))
        Set c = SrchRng.Find(str, , xlValues, xlPart, , , False, , False)

 

[DThib]

Thanks. Same result, does nothing.

In that case try

        Set SrchRng = Sheets("Inventory").Range("M2", Sheets("Inventory").Range("M").End(xlUp))
        Set c = SrchRng.Find(str, , xlValues, xlPart, , , False, , False)

 

[Fluff]

If you step through the code using F8 does it get to this line?

[FONT=Calibri][SIZE=3][COLOR=#000000]FirstAddress = c.Address[/COLOR][/SIZE][/FONT]

 

[DThib]

It skips over or more rightly, moves beyond it and runs through. Basically when I hit the search button, nothing happens.

 

[Fluff]

In that case it can't find the TB1 value in col M. If it should be there, check the spelling.