Displaying Search Results

[kruck88]

Hello All,

I am very green to VBA and the only things I know are self taught. I am creating a database for work with the location of our molds. It is going to be used by the guys down in the shop, so it needs to be very intuitive. Through online searching I have put together a code that is 90% of what I want. I can make it display what row the result of my search is on, but not the rack number. The Rack numbers are in Column A, and mold numbers populate columns B,C, and D.

I have attached the code below.


Sub Commandbutton1_()

Dim Prompt As String
Dim RetValue As String
Dim Rng As Range
Dim RowCrnt As Long

Prompt = ""

With Sheets("Sheet1")

Do While True

RetValue = InputBox(Prompt & "Mold Number?")
If RetValue = "" Then
Exit Do
End If

Set Rng = .Columns("B:D").Find(What:=RetValue, After:=.Range("B1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Rng Is Nothing Then
Prompt = "I could not find """ & RetValue & """"
Else
RowCrnt = Rng.Row
Prompt = "I found """ & RetValue & """ on row " & RowCrnt
End If
Prompt = Prompt & vbLf
Loop

End With

End Sub

In summary, I want to display the value in column A for the row that the mold number was found on.

[SamT]

Kruck,

One of my personal peeves is people who try to use Excel as both a data entry form and a Data Storage system while trying to keep Hamfisted users from messing with their workbook.

I have attached workbook with a real simple MS Forms Data entry form. It is not coded yet, all it does right now is present some info for the user to pick from.

When coded, clicking on the "Save and Continue" button would write the info the user seklected into the DataStorage Sheet.

Even as is, you can modify the info on the Lists sheet; First X out (close with the X button) the Data input Form, Edit Lists, then save and reopen the book to see your changes show up in the Form.

It won't take much coding to make this a real application for you. I only spent about 5 minutes on it so far. About as long as to write this.

[kruck88]

Sam,

I will take a look at what you have. I had to post again before it would let me. The people on the floor will not be entering any data, that will be my responsibility. The only thing they will be doing is entering a mold number into a search option and reading what rack is displayed.

[SamT]

As I understand it, you have a row for each rack with the Rack number in col A and the other columns in that row have mold numbers in them.

Well, whether you decide to use a Worksheet or a User Form, you still need to find the Rack number.
Change this line
Prompt = "I found """ & RetValue & """ on row " & RowCrnt
To
Prompt = "I found """ & RetValue & """ on Rack " & Range("A" & Cstr(RowCrnt))