Complex match or maybe a vlookup on users from


Board Regular
Jun 13, 2015
Office Version
  1. 2019
  2. 2010
  1. Windows
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)()

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.

VBA Code:
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 = ""
            For iText = 1 To 8
                Me.Controls("txtItem" & iText) = .Cells(rowIndex, iText + 1)
        End If
    End With
    ActiveWorkbook.Close False '<--| close opened workbook
'    Application.ScreenUpdating = True
End Sub

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Latest member