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?
Thank you in advance.
DThib
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?
Code:
[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