kruck88
03-11-2013, 05:47 AM
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.
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.