PDA

View Full Version : Solved: Data Query



Rlb53
02-11-2012, 09:43 PM
I have a Userform.

Within the Userform is a Combobox which lists Criteria to be searched for within a table.

I'm trying to search the table and return the information that is included within the entire row of each row that contains a cell with the Matching Criteria of the Combobox and paste it into an adjacent table.

The Matching Criteria may be found in 1 of 10 Columns to be searched.

Multiple Rows with associated data may be returned, but then can be copied and pasted into an adjacent worksheet to allow the information to be returned to the userform textbox1 (thru 10) for review.

I can do this with multiple Loops providing for Sorts, Copy and Paste but I'm trying to compact the process.

I was going to post the code i have constructed, but elected not to because it is Loooooong !

Could you offer assistance?

p45cal
02-12-2012, 10:52 AM
Attach the file (or a cut down version of it containing just what's necessary - including existing code for the job in hand) here. Most potential responders to this question won't bother to re-create your set up and guess at what you have.

Rlb53
02-12-2012, 11:22 AM
Thank you P45 !

I have attached the file I'm working on.

As mentioned, it "Almost" works as it is, but with a hiccup.

The search criteria is held in 1 of 10 columns.

The search returns the data requested when it is contained in columns 2 through 10, but not when it is located in column1.

Thank you .

p45cal
02-12-2012, 05:15 PM
I've run out of time 'til tomorrow, so here's something to get your teeth into; you can try temporarily replacing your two macros with these:
Private Sub CommandButton_VendorSearch_Click()
Dim CellsFound As Range
ListBox1 = ""
VendorID = ""
Address1 = ""
Address2 = ""
City = ""
State = ""
Zip = ""
Phone1 = ""
Phone2 = ""
Fax = ""
Email = ""
Cell = ""
Contact = ""
Product1 = ""
Product2 = ""
Product3 = ""
Product4 = ""
Product5 = ""
Product6 = ""
Product7 = ""
Product8 = ""
Product9 = ""
Product10 = ""

'Application.ScreenUpdating = False
Sheets("Data1").Unprotect

If ComboBox_DescriptionList.Value = NullString Then
MsgBox "Select an Item to Search"
ComboBox_DescriptionList.SetFocus
Exit Sub
End If
With Sheets("Data1").Range("Product1", "Product10")
Set c = .Find(What:=ComboBox_DescriptionList.Value, LookIn:=xlFormulas, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set CellsFound = Union(c, IIf(CellsFound Is Nothing, c, CellsFound))
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
If Not CellsFound Is Nothing Then
Set xx = CellsFound.EntireRow
Sheets("Vendor Search List").Cells.ClearContents
xx.Copy Sheets("Vendor Search List").Cells(2, 1)
With Me.ListBox1
.Value = ""
.RowSource = Sheets("Vendor Search List").Range("B1:B50").Address(external:=True)
End With
End If
End With 'Sheets("Data1").Range("Product1", "Product10")
End Sub
and
Private Sub CommandButton1_Click()
On Error Resume Next
With Sheets("Vendor Search List")
With .Range("b2", .Range("b" & .Rows.Count).End(xlUp))
Set tt = .Find(ListBox1.Value)
VendorID.Value = tt.Offset(0, -1)
Address1.Value = tt.Offset(0, 1)
Address2.Value = tt.Offset(0, 2)
City.Value = tt.Offset(0, 3)
State.Value = tt.Offset(0, 4)
Zip.Value = tt.Offset(0, 5)
Phone1 = tt.Offset(0, 6)
Phone2.Value = tt.Offset(0, 7)
Fax.Value = tt.Offset(0, 8)
Cell.Value = tt.Offset(0, 9)
Contact.Value = tt.Offset(0, 10)
Email.Value = tt.Offset(0, 11)
Product1 = tt.Offset(0, 12)
Product2 = tt.Offset(0, 13)
Product3 = tt.Offset(0, 14)
Product4 = tt.Offset(0, 15)
Product5 = tt.Offset(0, 16)
Product6 = tt.Offset(0, 17)
Product7 = tt.Offset(0, 18)
Product8 = tt.Offset(0, 19)
Product9 = tt.Offset(0, 20)
Product10 = tt.Offset(0, 21)
End With
End With
End Sub

Rlb53
02-12-2012, 10:51 PM
Wow... I'm going to check it out now...

Will let you know.

Thank you for the Education !

p45cal
02-13-2012, 06:19 AM
I see you've marked this as 'Solved' but as it stands it's not very robust.
If you had two companies with the same name (not unheard of, eg. branches of the same company in different places) then in the listbox, you'd only stand a fifty/fifty chance of it showing the right one. With more companies of the same name, the chance would go down further.
You have a Vendor ID column which I presume can't have duplicate IDs in, you should use this to identify rows to have in the listbox.

If, with the code you have at the moment (my stuff), you duplicate 'PLUMBPROS PLUMBING, LLC* (row 607) but tack the letter 'b' onto its Vendor ID (and a few other fields to make it easy to identify which is being shown in the user form), then make other changes in the Product columns, so that:
both companies have one Test2,
but only the second (new) one has a Test1.Two anomalies crop up:
1. When searching for Test1, the wrong company is listed in the userform - it doesn't have a Test1.
2. When searching for Test2, the company appears twice in the listbox (good), but the details that come up are the same for both.

In the attached is some code that addresses this. If you like what you see then come back and I'll run through the changes that need to be made to the listbox1 control.
(Of course, I've probably broken some other buttons' functions in the process!)

Rlb53
02-13-2012, 07:02 AM
Thank you once Again P45 !

I hadn't thoroughly inspected the results of the previous code..... after closer review I see what you mean.

But.. with your returned file... at a glance it seems as though it is a different story !

Your work is Awesome ! I'm going to have the spouse run it through the mill and report back to me any inconsistencies they may find.

I've got numerous data acquisition / processing / projects that I've been working on to standardize some routines that I am presented with. All of them have been roughed together with my minimal skills in Excel. I'm wanting to convert them over to MSAccess to allow for remote data storage back into my primary server. If you have any suggestions of how to begin researching this process... your thoughts would be immensly appreciated.

I Thank you again for your incredible assistance!

Rlb53
02-17-2012, 05:52 PM
Hello P45,

Your help is Awesome and I wanted to let you know that I beat my way through the hiccup I was having to return values to my userform. (refer to the workbook reviewed)

I am numerically identifying the row in which new data is being placed as defined below:

ActiveCell.Value = VendorList.Value
ActiveCell.Offset(0, 1).Value = Address1.Value
ActiveCell.Offset(0, 2).Value = Address2.Value
ActiveCell.Offset(0, 3).Value = City.Value
ActiveCell.Offset(0, 4).Value = State.Value
ActiveCell.Offset(0, 5).Value = Zip.Value
ActiveCell.Offset(0, 6).Value = Phone1.Value
ActiveCell.Offset(0, 7).Value = Phone2.Value
ActiveCell.Offset(0, 8).Value = Fax.Value
ActiveCell.Offset(0, 9).Value = Email.Value
ActiveCell.Offset(0, 10).Value = Cell.Value
ActiveCell.Offset(0, 11).Value = Contact.Value
ActiveCell.Offset(0, 12).Value = Product1.Value
ActiveCell.Offset(0, 13).Value = Product2.Value
ActiveCell.Offset(0, 14).Value = Product3.Value
ActiveCell.Offset(0, 15).Value = Product4.Value
ActiveCell.Offset(0, 16).Value = Product5.Value
ActiveCell.Offset(0, 17).Value = Product6.Value
ActiveCell.Offset(0, 18).Value = Product7.Value
ActiveCell.Offset(0, 19).Value = Product8.Value
ActiveCell.Offset(0, 20).Value = Product9.Value
ActiveCell.Offset(0, 21).Value = Product10.Value
ActiveCell.Offset(0, -1).Value = "=(r[-1]c[0]+1)"
ActiveCell.Offset(0, -1).Select
Selection.NumberFormat = """MEV""00000"

I found the problem to lie in that the search format would not recognize the "Formula" placed into "ActiveCell.Offset(0,-1)" as a digit.

To overcome the condition I added a couple of lines to the code to Copy and PasteSpecial.xlvalues back into the cell after the numeric value had been determined from the formula previously inserted into the cell.

ActiveCell.Offset(0, 19).Value = Product8.Value
ActiveCell.Offset(0, 20).Value = Product9.Value
ActiveCell.Offset(0, 21).Value = Product10.Value
ActiveCell.Offset(0, -1).Value = "=(r[-1]c[0]+1)"
ActiveCell.Offset(0, -1).Select
Selection.NumberFormat = """MEV""0000#"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

If you know of a more appropriate manner to achieve this... I'm All Ears ! (or eyes as the case may be)

But, I wanted to let you know that I "Think" I have it worked out and again thank you for your assistance !

p45cal
02-17-2012, 06:10 PM
I think, but check, that this:ActiveCell.Offset(0, -1).Value = ActiveCell.Offset(-1, -1).Value + 1
Selection.NumberFormat = """MEV""0000#"is the equivalent of:
ActiveCell.Offset(0, -1).Value = "=(r[-1]c[0]+1)"
ActiveCell.Offset(0, -1).Select
Selection.NumberFormat = """MEV""0000#"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

But, re:"I found the problem to lie in that the search format would not recognize the "Formula" placed into "ActiveCell.Offset(0,-1)" as a digit."

If you're talking about the .Find not finding it, then if that .Find contains the term xlFormulas, change it to xlValues, so you might not need o get rid of the formula.