Using prompts to lookup rows

[Tcarey]

Hello,

I'd like to use prompts to allow a user to enter a NAICS code to look up a single row of data.

The excel file will look the the clip below with many more rows and a few more columns.


What I'd idealy like to do is have a blank page open with a prompt that they enter "111110" to bring back the information for soybean farming.

How should I go about creating this for myself?

Thanks for your help in advance. I hope to be able to contribute my own help to this forumn where possible.

<TABLE style="WIDTH: 407pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=541><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 235pt; mso-width-source: userset; mso-width-alt: 11446" width=313><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" span=2 width=82><TBODY><TR style="HEIGHT: 63.75pt; mso-height-source: userset" height=85><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 63.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=85 width=64>NAICS CODE 2007 6 Digit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 235pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=313>NAICS DESCRIPTION</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82>Average</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82>CB Average</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17 align=right>111110</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Soybean farming, field and seed production</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>5.54</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>6.11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17 align=right>111120</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Canola farming, field and seed production</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>5.78</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>7.56</TD></TR></TBODY></TABLE>

 

[tweedle]

Welcmome to the board.

Here's one method using vlookup.

This actually has a combobox in B4 that Jeanie doesn't show.
Linked Cell: $B$4
ListFillRange: Sheet4!A2:A4

Excel Workbook
	A	B	C	D	E	F	G
3
4		111110	Soybean farming, field and seed production	5.54	6.11
5
Sheet3

Excel Workbook
	A	B	C	D
1	NAICS CODE 2007 6 Digit	NAICS DESCRIPTION	Average	CB Average
2	111110	Soybean farming, field and seed production	5.54	6.11
3	111120	Canola farming, field and seed production	5.78	7.56
Sheet4

 

[Tcarey]

I'm trying to stick with using a userform, but thanks for the suggestions.

The following code works ok for bringing in one specific NAICS code, but I would like for it to copy all of the Agriculture, Forestry, Fishing and Hunting to the new sheet. Any suggestions?


Sub UserForm_Initialize()
ComboBox1.List = Array("Agriculture, Forestry, Fishing and Hunting", "Mining, Quarrying, and Oil and Gas Extraction", "Utilities")
End Sub
Private Sub ComboBox1_DropButt*******()
Dim myString As String
Dim foundCell As Range

myString = Trim(UserForm2.ComboBox1.Value)
If myString = vbNullString Then
Exit Sub
End If

' Find and copy entire row.
With Sheets("Risk Early Warning System").Range("B:B")
Set foundCell = .Find(What:=myString, After:=.Cells(1, 1), LookAt:=xlWhole)
foundCell.EntireRow.Copy
End With

' Paste copied cell.
Sheets("Risk").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial


End Sub

 

[Tcarey]

New direction. I'd like to hide the sheet that the range is being found on. How do I change the active sheet.cells.find portion to find from a specific hidden sheet?

Also once the range is copied what is the correct code to have the range pasted into a different sheet?


Sub UserForm_Initialize()
ComboBox1.List = Array("Agriculture, Forestry, Fishing and Hunting", "Mining, Quarrying, and Oil and Gas Extraction", "Utilities")
End Sub
Private Sub ComboBox1_DropButt*******()
Dim Found As Range, FirstFound As String, AllRows As Range
If ComboBox1.Value <> vbNullString Then

Set Found = ActiveSheet.Cells.Find(ComboBox1.Value, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Found Is Nothing Then
MsgBox "No match found.", vbCritical, "No Match"
Else

FirstFound = Found.Address
Set AllRows = Found.EntireRow

Do
Set Found = Cells.FindNext(Found)
Set AllRows = Union(AllRows, Found.EntireRow)

Loop Until Found.Address = FirstFound

AllRows.Copy

End If
End If
End Sub