VBA help to lookup using a user input to search a column, and return value


New Member
Sep 21, 2014
New here and need a little help if anyone can help?

I have a 2 spreadsheets,

  1. Spreadsheet named "SOH Pronto", this is my data source which I update periodically, it holds product information, description, brand, supplier, price etc.
  2. Spreadsheet named "Pronto Query", which is the file the user will use it contains the following worksheets;

  • DASHBOARD : Just a worksheet with user instructions and button
  • DATA: a table generated with a connection to the spreadsheet "SOH Pronto"
  • TICKET: a spreadsheet where I wish to display my results onto a user form which I can print out.

What I wish to do is once the user clicks on the button an input box pops up requesting the user to input a unique identifier. In this instance it will be stored as userPronto, I then wish to search a sell range in worksheet DATA! in column AE and validate if the inputted data is in the cell range of that column. If it is not found I want to return an error message to the user. If it is found I then wish to store userPronto as itemCode then search the adjacent colums to retrieve data from specific columns,

for example the user inputs 19066, this is locataed at DATA!AE5, I know the other data I need is in loctation A5 (supplier part number), D5 (Brand), E5 (Description 1), K5 (Retail Price), L5 (Promo price), Q5 (Description 2), AG5 (Online y/n)

so far I have the following VBA, which will return the users input, just not sure where to go from there?

Sub myPronto()
Dim ws As Worksheet, myCounter

Dim userPronto As String
Dim itemCode As String

Dim rowLookup As String
Dim Brand As String
Dim itemDesc1 As String
Dim itemDesc2 As String
Dim onlineItem As String
Dim retailPrice As String
Dim promoPrice As String

userPronto = ".." + Application.InputBox("Input Pronto No. exluding the '..'")

itemCode = userPronto

Range("ticket!A1").Value = itemCode

End Sub

Anyway once i am able to store the results of each cell I wish to display them in a user form in the TICKET worksheet
which I can then print out. Basically creating a ticket,


Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi, and welcome to the forum.

When I code forms I like to use a global variable for the row the current record is on.

Rich (BB code):
'global variable - row the record is on
Public currentRow As Long

And separate the FIND routine into a function.
Rich (BB code):
Function ReturnRow(ByVal FindText As String, _
                   ByVal SheetName As String, _
                   ByVal SearchColumn As String) As Long
   'returns row the text was found on
   Dim rngFound As Range

   On Error Resume Next
      With Sheets(SheetName)
          Set rngFound = .Columns(SearchColumn).Find(What:=FindText, _
                          After:=.Cells(1, SearchColumn), _
                          LookIn:=xlValues, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
      End With
   On Error GoTo 0

   If rngFound Is Nothing Then
      ReturnRow = 0
      ReturnRow = rngFound.Row
   End If
   Set rngFound = Nothing
End Function

The function takes the search value, search worksheet and search column as arguments.
And it returns the row the search value is on.
We then use this to populate the "ticket" sheet
Rich (BB code):
Sub MyPronto()
   Dim userPronto As String
   'find the row the record is on
   userPronto = ".." + Application.InputBox("Input Pronto No. exluding the '..'")
   currentRow = ReturnRow(FindText:=userPronto, _
                          SheetName:="Data", _
   'If the search value is found
   'populate the ticket worksheet
   If currentRow = 0 Then
      MsgBox "Seach value not found"""
      Exit Sub
      'populate ticket worksheet
      With Sheets("Ticket")
         .Range("A1").Value = Sheets("Data").Range("A" & currentRow).Value 'suplier par tnumber
         .Range("A2").Value = Sheets("Data").Range("D" & currentRow).Value 'brand
      End With
   End If
End Sub

Hope this helps
Upvote 0
Bertie, your a legend.

I extended the source to include the remainder of data I need. Just one hiccup, I can't attach here my example, but the reason
for this project is I have a Word template, which with mail merge will let me port this information in to generate a poster style
ticket. Not sure though for the RRP and Promo price field to port it out in decimal form but still keep it as a text string. At the moment
the result is rounded out, i.e $83.75 is shown as $83.80 and $39.95 will show as $40 etc... If I can fix that I then should be able to
merge the information into the word DOC ticket template. The only other way I was thinking was to generate a user form and populating
the information on it and formatted as required so I can then print it from there. However, the word DOC I have is already formatted
specifically for the task so it would make sense to create it that way?
Upvote 0
I fixed the decimal point issue, now just need to work out how to transfer data from
Ticket! A2 - AH based on the data pulled based on the users input to my word template
DOC, which has labels already in it I have named the file name is;



Upvote 0
Sorry I have taken so long to get back to you.

For future reference you can assign the number format of cells with vba.
Rich (BB code):
      'populate ticket worksheet
      With Sheets("Ticket").Range("A1")
         .Value = Sheets("Data").Range("A" & currentRow).Value
         .NumberFormat = "£#0.00"
      End With

The code below is untested and is for illustration only.
The way you export data from Excel to Word depends on how you set up the Fields in MS Word.
Rich (BB code):
Sub UntestedSampleCode()
   Dim wordApp As Object
   Dim wordDoc As Object
   Set wordApp = CreateObject("Word.Application")
   Set wordDoc = wordApp.Documents.Open("c:\temp\FieldTest.docx")
   wordApp.Visible = True
   'Form Field Example
   With wordDoc
      .FormFields("FieldName01").result = Sheets("Data").Range("A" & currentRow).Value 'suplier par tnumber
   End With
   'Bookmarks Example
   With wordDoc.bookmarks
      .Item("FieldName01").Range.InsertAfter = Sheets("Data").Range("A" & currentRow).Value 'suplier par tnumber
   End With

   Set wordDoc = Nothing
   Set wordApp = Nothing
End Sub
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back