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

[mattkle]

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
'VLOOKUP(userPronto,DATA!AE1:AE9,2,FALSE)


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,

Thanks.

 

[bertie]

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.

'global variable - row the record is on
Public currentRow As Long

And separate the FIND routine into a function.

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, _
                          SearchFormat:=False)
      End With
   On Error GoTo 0


   If rngFound Is Nothing Then
      ReturnRow = 0
   Else
      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

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", _
                          SearchColumn:="AE")
                          
   '===================================
   'If the search value is found
   'populate the ticket worksheet
   '===================================
   If currentRow = 0 Then
      MsgBox "Seach value not found"""
      Exit Sub
   Else
      'populate ticket worksheet
      With Sheets("Ticket")
         'EDIT OTPUT RANGES ON TICKET SHEET
         .Range("A1").Value = Sheets("Data").Range("A" & currentRow).Value 'suplier par tnumber
         .Range("A2").Value = Sheets("Data").Range("D" & currentRow).Value 'brand
         '
         'etc
         '
      End With
   End If
 
End Sub

Hope this helps
Bertie

 

[mattkle]

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?

 

[mattkle]

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;

ticketTemplat_prontoQuery.docx

Regards,

mattkle

 

[bertie]

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

For future reference you can assign the number format of cells with vba.

      '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.

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
      'etc
   
   End With
   
   
   '==========================
   'Bookmarks Example
   '==========================
   With wordDoc.bookmarks
      .Item("FieldName01").Range.InsertAfter = Sheets("Data").Range("A" & currentRow).Value 'suplier par tnumber
      'etc
   End With


   
   Set wordDoc = Nothing
   Set wordApp = Nothing
End Sub