Solved: Excel Database Search Engine

[xfitguru]

Hi everyone. This is my first cry for help as I am in desperate need. I am trying to create a search engine that will find all Part Numbers assigned to a vendor and show the results in a textbox on a new userform. I have provided the workbook I am using. This is my first VBA project so I encourage as much constructive criticism as possible. Thank you everyone and God bless!

I have 2 hidden worksheets. One powers the file search which provides the hyperlink to navigate for one of the buttons on the userform. The other sheet provides the table which lists the items associated with the referenced Vendor name. Am I going about this all wrong? Maybe someone can suggest better logic. Thanks everyone. :)

[Bob Phillips]

1) you didn't provide a workbook

2) is this homework?

[xfitguru]

My apologies xld - I have attached the workbook. This isn't homework. It is a side project I am doing for work to improve the way our Sales force views these files. I sincerely appreciate all the help!

[Teeroy]

Here is a quick tidy up of one section of your code (to get the list of products per supplier). I would suggest a heading to show the supplier whose products are being viewed.
Two questions:
- Did you consider just filtering the list in situ rather than creating a copy?
- If you stick with the copying are you planning to use another another userform to kill the copy after it is viewed? It would be easy to end up with many copies open.

Also it is not really good form to redim a global variable locally as a diferent type (i as integer globally, long locally).

Private Sub CommandButton4_Click()
Dim last_row As Long, i As Long
Dim sht As Workbook
Dim rng As Range
Set sht = Workbooks.Add()

Set rng = sht.Sheets(1).Range("A1")
last_row = ThisWorkbook.Sheets("Berry Letter Item Database").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To last_row
If frmTOC.ListBox1.Value = ThisWorkbook.Sheets("Berry Letter Item Database").Cells(i, 1).Value Then
ThisWorkbook.Sheets("Berry Letter Item Database").Range("b" & i & ":c" & i).Copy
With rng
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
sht.Sheets(1).Name = "Search Results"

Set rng = rng.Offset(1, 0)

End If
Next i
Set sht = Nothing
Unload Me
End Sub

[xfitguru]

Thanks Teeroy! Much appreciate any and all replies - Especially with suggestions for improvement. I did consider the list but wanted to give more of a web based/windows environment for the people who will be using this as.

I actually want to get rid of the additional workbook copy altogether and simply have the results populate in a second userform textbox. When that userform is unloaded with a 'Cancel' or 'Close' button, it would clear the textbook for the next search. Like I said, I may be going about this thing all wrong so I am open to suggestions. Thanks again for the code Teeroy, it worked.

Any thoughts on the userform instead of a new open workbook, and also to have this code work faster? Thanks

[xfitguru]

Bump - Please help.....? Anyone....... :(

[Bob Phillips]

Try this

[Bob Phillips]

A faster version

[xfitguru]

Thank you so much XLD. You are the man. I appreciate the help with this one. I'm marking this solved.

[snb]

For an alternative approach