Use a form to extract data from spreadsheet

[dab1477]

I an inputting data into a spreadsheet via a form I created. I would like to use a same/similar form to extract data from the spreadsheet. The spreadsheet contains 37 variables that I wish to extract.

I want to do the following;
Enter a machine # into a field
Enter a part # into a field
I want to search the database for the entered machine and part # for a MAX date. I want to view the 37 variables in a form type format - not a spreadsheet. I realize that I need to create a form, of which I can do. If I can get a macro that returns the 37 variables, I can get them into a format that I can use. How do I search my spreadsheet for 2 matching fields to get the most recent date's data?

Thanks in advance. I know this is better in Access, but Access isn't widely understood by those that will use this data, ergo my EXCEL sheet.

 

[devonknows]

How is your data stored, are they in seperate columns like so

Machine Number / Var1 / Var2 / Var3 and so on?

or is your data indexed so sheet one has all your machine numbers and sheet two has all the corresponding numbers and additional data.

need more information, but the best thing i would suggest it to create a text box for the machine # and have all the rest of the controls disabled except one button with its Default value set to true.

Next you would use a VBA MATCH to locate your machine number if your data was all in columns next to each other on one sheet would be easier so when the buttn is pressed a macro code could be:

Public Function Tot(asd) As String

    If (TextBox1 = "") Then Exit Sub
    If Not IsNumeric(TextBox1) Then Exit Sub
    row = pFindRowPos(TextBox1)
    
    'Fill the Other Fields
    TextBox2 = Range("B" & row)
    TextBox3 = Range("C" & row)
    'etc etc etc
    

End Function
Private Function pFindRowPos(sText As Variant, _
    Optional SearchDirection As XlSearchDirection = xlNext, _
    Optional SearchOrder As XlSearchOrder = xlByRows) As Long

      Dim lResult As Long, oRg As Range

      Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
      LookAt:=xlPart, SearchOrder:=SearchOrder, _
                   SearchDirection:=SearchDirection, _
      MatchCase:=False, SearchFormat:=False)

      If Not oRg Is Nothing Then lResult = oRg.row

      pFindRowPos = lResult

      Set oRg = Nothing

  End Function

Regards,

 

[dab1477]

Thanks Devonknows,
I'm not VBA savy, but I will mull this over. You are correct that my data is in columns -Machine Number / Var1 / Var2 / Var3
Before I run the code you suggested, I believe I must run a MAX function for the DATE variant. I'm thinking as follows:

Enter "Machine"
Enter "Part #"

Search database for "Machine", filter for "Part#", return MAX from Date Variant.

I believe I then use your code to fill in the other variants - correct?

Thanks for bearing with me. My VBA skills are elementary at best. I appreciate your input.

 

[dab1477]

Thank you