Complex match or maybe a vlookup on users from

[jvoss]

Good Day all,

I looking to be able to do a lookup and provide missing info and fill in the corresponding text boxes in the UF (example if i type in a company name the code can put the corresponding data in the correct place(ie addres/phone/email/website)); but if i have just the phone and i put that in the phone "text box" and it fills in the corresponding info, and if two or more listing have the same phone number then let user choose the one they are looking for. this should be able to work the same with email, address, and even zip code. (JUST USEING COMPANY INFO AS AN EXAMPLE)

this is all done on exit of corresponding text box.

Here is what i have so far, and here is what is happening. (this is only working on one direction.) (but needs to be able to work both directions)


the file opens from the UF
then (in the watcher Window) i set a break to see what rowIndex and txtItem are.

txtItem is the correct info (ie whats entered in the UF)
rowIndex is showing 2042 error..

when i look at the workbook it opened up and it has the 13th column from (A:A) or 6 column from txtItem ie(M:M) highlighted.
The item to match(txtItem) is in the 8 row ie(H:H)

sample info from workbookxxx

txtItem(H:H)	name(I:I)	(j:j)	(K:K)	(L:L)	number(M:M)	(n:n)	(o : o)	(p : p)	()
012346512					5545	asdfass	fffes	ssaeww	gexee

will this work with the code i have? or is this a complete re-wright or even a new function? any help would be usefull.

Private Sub txtItem_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'    txtItem.Value = Format(txtItem, "00000")

    Dim rowIndex As Variant '<____________________ not sure what to use "variant, string, double"  the value will be a mix of text and number up to 17 characters  long
    Dim iText As Variant
    
'    Application.ScreenUpdating = False

    With Workbooks.Open("Location of workbook").Worksheets("Sheet1").Range("H:AZ") ' or use an aray ("txtItem")<--| open needed workbook and reference its "Sheet1" "Lookup" range (change "Sheet2" to your actual sheet name)
        rowIndex = Application.Match(Me.txtItem.Value, .Columns(8), 0) '<--| try searching "Lookup" range first column for 'txtItem' value
        If IsError(rowIndex) Then 'check to see if value exists
            MsgBox "This is an incorrect Article Number"
            Me.txtItem.Value = ""
        Else
            For iText = 1 To 8
                Me.Controls("txtItem" & iText) = .Cells(rowIndex, iText + 1)
            Next
        End If
    End With
    ActiveWorkbook.Close False '<--| close opened workbook
'    Application.ScreenUpdating = True
End Sub
'https://stackoverflow.com/questions/42210238/vlookup-extract-info-from-different-excel-workbook