Search box within data entry form

[DangerDano]

Hello everyone! I'm a VBA newbie trying to design a simple data entry form for Excel, and thus far I've managed to tweak out examples of code to accomplish various tasks. However, I have yet to find a thread that addresses this specific problem, so hopefully someone can help me out.

I have a worksheet with several thousand entries, and the user needs to be able to search for an individual record and update information via the form. Ideally, I would like to create a "Search" button on the form that opens a new window where the user can enter either a single or multiple search criteria. The worksheet contains one column that acts as a unique identifier for each record, so in most cases only a single search criteria is necessary. However, several records may have a blank value in this column for one reason or another so the user would need the option of entering additional criteria to locate the desired information. There are two additional columns that can act as a unique identifier when they are combined, and I would like for the user to be able to also search for records based on these two values.

I have attached a sample worksheet to show the layout of the data and the code I've written so far.
Single Search Criteria = "Doc No" column
Multiple Search Criteria "Vol + Page" columns.

Thanks in advance for your help!

:helpDano:help

[mumin_abdul]

Instead of using programming via VBA - try looking into using PivotTables. Look it up on help - its easier and less messier and looks better than a form.

But if you still want to use a form I'll try to help you.

[mumin_abdul]

Highlight all the data in the sheet. Then Insert > PivotTable

Then select the data you want to see in the PivotTable Field List on the right pane.

Mess around with it to see how it works.

[DangerDano]

Thanks for the idea, but I don't think it will work for my purpose. I am creating a form for data entry purposes, and the desired Search command would be used to locate specific records and load their information into the form. The missing data could then be entered and saved, thus updating the individual record. I'm not sure if PivotTables could be used to accomplish this, even so I would prefer to use VBA as future projects will build on this. Thanks again for your input, and I would greatly appreciate if you wouldn't mind helping with the code.

Dano

[mumin_abdul]

Is it ok if I describe how the form would work using VBA? Im really not up to actually building a form for you. Sorry.

[mumin_abdul]

For the search, use:




Columns("D:D").Select

Dim RecordRow As Integer
RecordRow = Selection.Find(What:="gen", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row



This will retrieve the row of the search for "gen" in column D.

Then you would retrieve all the data from that row (number of the row is RecordRow) into the variables e.g.




Dim Vol As String
Vol = Cells(RecordRow, 5).Value

TextBoxVol.text=Vol


Then you would simply put each of these data into the relevant textbox on the form.

[mumin_abdul]

Do you have further questions?

[DangerDano]

I believe I understand the code that you posted, but it looks like it will only search based on values in column D. In the event that the value doesn't exist in column D, additionally I would like to be able to search for a unique value that is populated from columns E & F (E_value + F_value = new_value). If this does not make sense, please let me know and I will try to explain further. Thanks again for your help!

[mdmackillop]

This is a bit messy (late night), and incomplete.
Use the combos, either Book or Volume then Page to get the unique row. Add to the Populate macro to read in more data. It will fill in Grantor, to which I've added an increment for debugging purposes.

[mumin_abdul]

Have you (DangerDano) had your question resolved through mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87)'s answer file?

[mumin_abdul]

I dont know of you still want a reply but here is a simple example of how to do it:



Set RecordRow = Range("D1:D10,E1:E10").Find(What:="gen", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If RecordRow Is Nothing Then

MsgBox ("no matches")

Else

MsgBox (RecordRow.Row)

Dim Vol As String

Vol = Cells(RecordRow.Row, 5).Value

MsgBox (Vol)

End If



Ive highlighted the most important parts.

[DangerDano]

Thanks again mumin_abdul, and sorry that I have not replied sooner. Do I need to change the three values that you highlighted to fit the values in my worksheet? I understand changing the ranges, but what value should I enter for What:="gen" and LookIn:=xlValues? Please advise. Thanks.

:helpDano:help

[mumin_abdul]

"Gen" is the text you are searching for in the worksheet so for example if you wanted to search for "Hello" you would replace "Gen" with "hello":



Set RecordRow = Range("D1:D10,E1:E10").Find(What:="hello", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If RecordRow Is Nothing Then

MsgBox ("no matches")

Else

MsgBox (RecordRow.Row)

Dim Vol As String

Vol = Cells(RecordRow.Row, 5).Value

MsgBox (Vol)

End If



xlValues means you are looking at the actual values of the cells instead of the formulaes e.g.

H2 = 50

I2 = 50

L2 = H2 + I2

L2 has a formula in its cell. The formula is "H2 + I2". If you searched with



Set RecordRow = Range("D1:D10,E1:E10").Find(What:="H2+I2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

...it would look for "=H2+I2" forumlae in the given range.

For example if L2 's value is 100 and it got from H2 + I2 where H2 is 50 and I2 is 50.

Then the formula of L2 is H2+I2, the value of L2 is 100.

What you want to do is search all the data on the worksheet and whenever it finds a match - it should save the row of the matched cell into a variable and use that value to extract all the rows data in each column.

My previous example showed how to extract the volume data from the worksheet after finding a match to a search term.

What you want to do is somehting like this:




Dim SearchTerm as String

SearchTerm ="gen"

'Change the searchterm ot whatever you want.
'Example: SearchTerm = TextBox1.Text

Set RecordRow = Range("D1:D10,E1:E10").Find(What:=SearchTerm, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If RecordRow Is Nothing Then

MsgBox ("no matches")

Else

MsgBox (RecordRow.Row)

Dim Vol As String

Vol = Cells(RecordRow.Row, 5).Value

'Extract other columns data in the same manner as the volume given here.

MsgBox (Vol)

End If



Do you have further questions?

[mdmackillop]

and post #9?

[DangerDano]

and post #9?
Thanks mdmackillop for your input, but it's not quite what I am looking for. The final spreadsheet will have thousands of records, with the "Vol" ranging from 1-1500 and "Page" ranging from 1-1000. Instead of a drop-down menu, I would like the user to be able to enter the "Vol" and "Page" into a search box and then populate a form with the rest of the missing information.


Do you have further questions?
The "No Matches" MsgBox is only displayed if the first search criteria is not met. I would like to be able to search for a combination of "Vol+Page" which is a unique identifier for each record. As it stands, if there are multiple records with the same value for "Vol" and a different value for "Page", it will return the first entry that matches the "Vol" criteria without considering the "Page" criteria. I believe this is a result of SearchDirection:=xlNext written into the Find event.

Is it possible to search for a record based on two criteria? That is the end result that I am looking forward, but if its not possible I can find a way to adapt the current code.

Again, thanks to the both of you for taking time to help me with this task and I apologize for the delay in my responses.

Dano

[mdmackillop]

Did you try entering the search data in the combo?

[DangerDano]

Thanks for pointing that out. I will try to adapt if for my needs and post any questions accordingly.

Dano

[mumin_abdul]

What you can do (there may be a better method), is to search for the first criteria and if it finds a match - check the other criteria in the relevant column in the same row as the first match. So itll be a loop within a loop.