Free Microsoft Excel 2013 Quick Reference

Creating a Userform to Search for Data

To anyone that can help,

I'm pretty new to VB and anything I know about it has been picked up as I go along.

I'm trying to create a userform which acts as a filter/search form of sorts. I need it to be in a userform format so I can't just use the standard filters in excel. What it is is there is a userform with the following fields: Project Name, Project Number, Project Value, Plant, Region, Engine, IPT Lead/Buyer, Status, Quarter. The user fills out the form either manually or by options in dropdown menus and then presses search. What I need to understand how to do is how to code the form to find whatever has been input into the different sections. The form will be searching through a large spreadsheet in excel.

My first thought was that because each of those fields in the large spreadsheet are always in the same column, there could be some way of of specifying ranges to match the words or something of that nature.

Any help would be greatly appreciated.

Thanks in advance.


Post your answer or comment

comments powered by Disqus
Hi guys I hope someone can help me!

I am using Excel 2003 and want to use a userform to search for data in the spreadsheet.

In short i haven't got a clue how to do it. My table is 9 colums by about 100 rows. What I want to do is to pick a value in the table and for the form to show me all the results that match it. Ie if it was a table about personal details by picking a surname (say smith) the form would show me all details on everyone called smith

Is this possible?
Thank you for all your help in advance!!!!!

Hi,

I need to create a macro to search for a value or certain conditions to the value entered in a particular cell. The search will be made down a column and when the data corresponding to the value or condition is found, I would like to highlight the particular row with color.

For instance, entered value in cell A1 is 240,
i would like to search down column D row 1 to row 100 for the first value that is equal to or exceeds the value in cell A1 (ie. 240). Once the value is found, I would like to highlight the particular row.

Appreciate if someone is able to help. Thanks very much in advance.

Dear experts,

Please I need a help in how to create a userform with a listbox or a combobox that when i choose any item from the list, it will give a table with the subcatgories of the selected items and all its information in the following way:

The listbox for example will have the following items to pick:

Cars
Books
Roads

and if i select Cars it will give me the following table on a popup userform or even in excel table if the popup userform is difficult:

Brand Color Horse Power Speed Model 4Wheel Mercedes Black 125 250 2012 No BMW Red 140 240 2011 No Infiniti White 160 220 2010 Yes
And then if i choose different item like books it will hide the table above and show another similar table.

Is this possible?

Thank You in Advance
AttalaEA

Hi,

I´ve created a Userforum with several txtboxes, then i added a cmdbutton, that when clicked, transfers all the data inserted in to a sheet named "DataBase".

Now i need to create another userform to search the data inserted.

For example: I've inserted data regarding to a client with the serial number 00555 three months ago. My new userform needs to be able to locate the data inserted in the "DataBase" sheet by searching the serial number.

I just can´t create the code.

Can anyone help me?

Thanks

hi,
i have prepared a form which searches for data items in a sheet and displays the results in a listbox, have tried much myself but whatever i've done isn't working for me, so any help will be greatly appreciated. also went through previous posts but solutions not wrking for me...

I have a macro that searches for data in worksheets data1 - data5 in the same workbook.
How to change the macro to the data searched in all the worksheets and workbooks ?

My Marco:
Sub Search()
    Dim xlForm As Excel.Worksheet, ostForm As Long
    Dim xlWks As Excel.Worksheet, ostDane As Long
    Dim arWks As Variant: arWks = VBA.Array("Data", "Data2", "Data3", "Data4",
"Data5")
    Dim i As Integer
   
    Dim RngS          As Excel.Range
    Dim RngCrit       As Excel.Range
   
    Set xlForm = ThisWorkbook.Worksheets("Search")
    With xlForm
        ostForm = Last(.Columns("A:J"))
        If ostForm > 8 Then .Range("A8:J" & ostForm).Clear
        Set RngCrit = .Range("A1").CurrentRegion
    End With
   
    Application.ScreenUpdating = False
   
    For i = 0 To UBound(arWks)
        ostForm = Last(xlForm.Columns("A:J")) + 1
       
        Set RngS = ThisWorkbook.Worksheets(CStr(arWks(i))).Range("A2").CurrentRegion
        RngS.AdvancedFilter Action:=xlFilterCopy, _
                            CriteriaRange:=RngCrit, _
                            CopyToRange:=xlForm.Range("A" & ostForm), _
                            Unique:=False

        xlForm.Rows(ostForm).Delete
    Next
   
    Application.ScreenUpdating = True
   
    Set RngS = Nothing
    Set xlForm = Nothing
End Sub
regards
Kiluk

Hopefully someone can help - I am new-ish to VBA...

I have created a UserForm to fill in data in a worksheet. I have a command button at the bottom and when I click on it, I would like it to take the data from the UserForm to populate the currently active worksheet. So clicking on the command button should do this:

- Find the last row with data in the currently active worksheet in Column L
- Go to the row below
- Insert the data from the first textbox in the UserForm
- Go to the next column (same row)
- Insert the data from the second textbox in the UserForm
- Go to the next column (same row)
- Insert the data from the third textbox in the UserForm
- Close the UserForm

Really hope that makes sense because I couldn't upload the file

Thanks in advance,

Natalie

To Whom...

Is possible to search for data in comments? When I search in Excel, it only
find data in the cells.
--
dmm129

i am loking to create a userform that contains the fields

Rec No,

Date

Name

Part Rec1

Part Rec 2

Part Rec 3

Part Rec 4

Part Rec 5

Part Rec 6

Part Rec 7

Part Rec 8

Part Rec 9

Part Rec 10

The form would have a submit changes button and a close form button

as the item codes are entered into the Part Rec fields they would automaically fill in a Part rec Description using vlookup for a datasheet next to these fields would be the amount requisitioned from stock

I am also looking to produce a similar Userform for deliveries and returns - all data would be submitted to a stock movement sheet and therefore the form would need to find the next available line to submit data to

if you require an example please ask

many thanks in advance

john dalton

Greetings,

I have created a simple userform to add data to an Excel 2007 worksheet; the form contains two buttons, one to add new rows of data and the other to close the form. The worksheet has been populated with data by users using the userform and now I'd like to add functionality to allow the users to search for data, and to edit data that was found during their search, in addition to adding new rows. My code appears below - I've removed many of the fields that show up on the userform to simplify the code (there are actually 74 columns). Thanks in advance for any assistance.

Best Regards,

Paul
*********************

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ContractData")

'find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'ensure user has entered a Requested Document Type

If Trim(Me.cboDocTypeReq.Value) = "" Then
Me.cboDocTypeReq.SetFocus
MsgBox "Please enter a Requested Document Type"
Exit Sub
End If

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.cboDocTypeReq.Value
ws.Cells(iRow, 2).Value = Me.txtFinDocNum.Value
ws.Cells(iRow, 3).Value = Me.cboInstitution.Value
ws.Cells(iRow, 4).Value = Me.txtRefDocNum.Value
ws.Cells(iRow, 5).Value = Me.txtProjStDate.Value
ws.Cells(iRow, 6).Value = Me.txtProjDur.Value
ws.Cells(iRow, 7).Value = Me.txtPRNum.Value
ws.Cells(iRow, 8).Value = Me.cboRequester.Value

'clears the data from the form

Me.cboDocTypeReq.Value = ""
Me.txtFinDocNum.Value = ""
Me.cboInstitution.Value = ""
Me.txtRefDocNum.Value = ""
Me.txtProjStDate.Value = ""
Me.txtProjDur.Value = ""
Me.txtPRNum.Value = ""
Me.cboRequester.Value = ""
Me.cboDocTypeReq.SetFocus
End Sub

'close form

Private Sub cmdClose_Click()
Unload Me
End Sub

'force use of button instead of using "X" to close form

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

I created a userform with two text boxes in which the user enters two different dates. the first text box searches for dates in a vertical column and pulls the row number where the match is found. The second text box searches for dates in a horizontal row and pulls the column number where the match is found.

the problem is the format of the dates in teh column are different from that of the row. I am able to find the row number from the first text box, but I cant get the column number of the second text box.

below is the code (txtdate1 is the name of the first text box, txtdate2 is the name of the second text box)

also is attached of what the dates look like in excel


	VB:
	
firstdate = Me.txtdate1.Text 
secondate = Me.txtdate2.Text 
 
 ' find date in column A
With ActiveSheet.Range("A:A") 
    Set Rperiod = .Find(Format(firstdate, "DDDD, MMMM DD, YYYY"), LookIn:=xlValues, LookAt:=xlWhole) 
     
    If Rperiod Is Nothing Then 
        daterowno = 0 
    Else 
        daterowno = Rperiod.Row 
    End If 
     
End With 
 
 'find date in row 2
With ActiveSheet.Range("D2:IS2") 
    Set Rcolumn = .Find(Format(secondate, "DD-MMM-YY"), LookIn:=xlValues, LookAt:=xlWhole) 
     
    If Rcolumn Is Nothing Then 
        datecol = 0 
    Else 
        datecol = Rcolumn.Column 
    End If 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello all!

I 've created a userform to search an excel database (that is populated by another userform) that contains a date field. This field is formatted as mmm/yyyy so I can search it by month.
All was well until out of nothing my sub can't find specific months.

It finds the related data for january, march, june, july and november, but not any of the other months.
In order to try and debug it I've used the exact same data in the other fields of the db changing only the date field to the desired month.

If I use excel's own Find, everything works as it should, the months are found just fine.

Has anyone a clue on why this could happen?

Thanks in advance for all the help you can give.

-- Alex

I have created a userform which allows me to add records to a list. I now want to be able to create a userform to allow the user to serach that list and then to display the results.

I would like to be able to display the results in a preformatted sheet ie. a sheet with headers, titles etc already in place where the relevant fields of the search results can populate it.

I will need to be able to do searches that result in one record being displayed and also searches that will result in multiple records.

How can I achieve this?

Thanks.

Ok. I have a table of data, a 2D matrix if you will. I am trying to find the easiest way to write a function to search this matrix for a particular value. However, the thing that has stopped me is that I don't know how to search a particular column of the chart.

I start off with a cell that has the value I am searching for, and a cell that holds a value representing which column to search fo r it in. I want to know what row the value is found in. How would I do it?

Thanks for any help you can give.

Hi,

I have a large amount of txt files, with three columns of data. I would like to create a macro to import that data into an excel spreadsheet, and rearrange the data from being divided into three columns and 7 rows to 21 columns and 1 row.

PDA and Other 2 should not be importet.
Other 1 should be placed in the columns ending with AV
Other 3 should be placed in the columns ending with AD

I have received a macro from a collegue of mine, but he is using excel files as his data files which makes it a little bit to difficult for me to use his macro as an instruction. I have created a start of the macro, but I am having problems figuring out how to do the txt file import and the ending loop.

If anyone could/would help me, it would be greatly appreciated.

Thank you,
Erik

I was wondering if someone could help with my issue.

Is it possible to create a userform, then send the data entered in the userform to an email recipient?

Sorry if this is in the wrong section, but figured some programming was likely to be necessary.

Is there a way to use a UserForm to search for data rather than add it into the sheet? If not that is fine was just wondering, it's a very interesting tool.

Thanks for your time.

Hi there, i'm currently using this macro, which is helping me find data in one sheet and copying it to another. However I now need to search for the data across multiple worksheets and then copy it to one worksheet.

Dim LSearchRow As
Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

Sheets("V.Sat").Select
Range("A1").Select

'Start search in row 4
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = LSearchValue, copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then



'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Get Info").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("V.Sat").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."
Basically, once it's finished searching on the v.sat worksheet, it then moves to the q.sat, neither, q.dis and v.dis worksheets.

Any help would be really appreciated.

Hi,

I have a spreadsheet that I have to cut and paste many cells from every month to another spreadsheet for analysis. This is very time consuming. I have been reading forums and help sites trying to find a better way. I even bought a book, while interesting, was not written for a manager, but an engineer.

The monthly spreadsheet contains lots of text and formulas to arrive at a complex set of performance statistics for service employees.

The second (analysis) spreadsheet is formatted for ease of viewing the data to be analyised. My Question:

Is there a way to search a column for specific, static "identifying" text and, based on the results, pull some corresponding data (on the same row) into specific cells into the second spreadsheet... here's the killer, it should do this automagically and be kicked of FROM the second spreadsheet... The columns are always named the same but the rows will likely never correspond to the same district/region month after month.

I realize this is a bit wordy but, not sure how else to explain it. If I have to muddle through VBA or something, that would be worth the effort.

I have a spreadsheet with Outlook logs that I need to sort by date. Unfortunately, the entries are text and appear as follows:
6/28/2010 2:01:30 PM (2010-06-28 18:01:30 UTC)

I want to be able to delete all the text from the open parenthesis back to the beginning of the cell. Obviously I can do it manually for each cell, but there are about 3000 entries, so that isn't practical.

Assuming the following can be done, what I'd like to do is create a macro that looks for the open parenthesis, moves the insertion point to the right of it, selects all the text from that point back to the beginning of the cell, and then deletes it.

That would leave the text so that the year of the entry is first, and I can accurately sort it even if it remains formatted as text.

Unfortunately, I haven't been able to figure out how to get this done. The search function seems to work only to find a particular cell, not to search and create an insert within a cell. Any help would be greatly appreciated.

Is there a way to create a userform that will allow browsing to select
a file to import(similar to the get external data function)? I want to
be able to do this without the import file wizard so it is
automatically Delimited with the Space selected. So far I have not
found a way to do it and figure that it is only possible if the
filename is entered into a textbox and the rest is hardcoded. Anyone
have any ideas if this is possible?

Hi,

I have a user form which allows a user to search for a file (not necessarily excel file), and populate a textbox with the filepath. I would like to then create a shortcut to the file they have done the search for, within a specific folder.
I've tried opening files and then saving as an ".lnk" file, but this doesn't work. I can't figure out how to copy a file and then paste as a shortcut within the folder, nor can I work out how to just create a shortcut file using the previously selected link.

Can anyone help with this? Perhaps it can't be done?

Richard.

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22

May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks

Low

--
A36B58K641

I need to figure out a way to search a range of cells for "comments=". The
problem is that there are people's comments after the equals sign so the cell
may contain the text string with other text.

I then need to be able to remove the text string "comments=" and put the
remaining results into a different cell.

This one has really thrown me for a loop. The comments could be in any
column A through K. I can't even figure out how to find cells that CONTAIN
"comments=", everything I have tried has required an exact match of the
entire cell . If anyone could help I would really appreciate it.


No luck finding an answer? You could always try Google.