VBA Code to see if criteria in a user form matches to a row in a table

[dbkimber]

I'm trying to use a form to match criteria in entered in a user form to a row in a table. If box1 value is found in the table and the value in box2 if found in the cell to the right of the value in box1 and the value in box3 is found in the same row as the first two boxes values, then I want to load the value in box4 to specific place in a sheet. if something doesn't match i want to have a message box pop up saying something doesn't match.

 

[DanteAmor]

The following code assumes that the search is in columns A, B and C. The records begin in row 2.

Private Sub CommandButton1_Click()
  Dim a As Variant, i As Long, dic As Object
  
  a = Range("A2:C" & Range("A" & Rows.Count).End(3)).Value2
  Set dic = CreateObject("Scripting.Dictionary")
  dic.CompareMode = vbTextCompare
  
  For i = 1 To UBound(a)
    dic(a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3)) = i + 1
  Next
  If dic.exists(TextBox1.Value & "|" & TextBox2.Value & "|" & TextBox3.Value) Then
    MsgBox "Row Match " & dic(TextBox1.Value & "|" & TextBox2.Value & "|" & TextBox3.Value)
  Else
    MsgBox "Something doesn't match"
  End If
End Sub

 

[dbkimber]

Sorry in just getting started into VBA and probably tackling a bigger project than i should be at this time, i need a little more help. I think I've got everything adjusted for the names of me boxes and what not. where I'm hitting the snag is with adjusting the range "a = Range("A2:C" & Range("A" & Rows.Count).End(3)).Value2". i have the data in a table named Master on a sheet currently named sheet2 and the data starts in F2 on that sheet. How would i adjust for this?

 

[DanteAmor]

If box1 value is found in the table and the value in box2 if found in the cell to the right of the value in box1 and the value in box3 is found in the same row as the first two boxes values

You can specify in which column you have to search for each data.

 

[dbkimber]

sure, the table starts in cell F1, row 1 being the header row. column "F" being the column I'm looking to match the value of box1 to. If it finds a match in column f to box1 then it should look in the cell to the right (column "G") of the mach to see if box 2 matched that cell and if those 2 match it would need to look for the match in box3 in one of the cells to the right of the match to box2. (doesn't matter which column as long as its the same row) table looks like the one below

need to match the name with boxq then look to see if the code entered in box2 matches the cell to the right of where it found the name match of box1 and then scan the rest of that row for a match to box3

so if box1 said name2 and box2 said 1001 and box3 said PC2.X then it would perform a task that ill have to set up late and if it doesn't find a row that matches those 3 criteria then the message box would pop up with the error message.

Signer	code	Cert 1	Cert 2	Cert 3	Cert 4
name1	1000​	ADMIN	OP
name2	1001​	ADMIN	MAN	PC2.X	OP
name3	1002​	PC2.X	CONTROLS
name4	1003​	OP

 

[DanteAmor]

Try this.

Change Table1 to the name of your table.
Change "Cert 1" for the name of the column in H, change "Cert 4" for the name of the last column of your table.

Private Sub CommandButton1_Click()
  Dim r As Range, f As Range, exists As Boolean, cell As String
  
  Set r = Range("Table1[[#All],[Cert 1]:[Cert 4]]")
  Set f = r.Find(TextBox3.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      If Range("F" & f.Row) = TextBox1 And Range("G" & f.Row) = TextBox2 Then
        exists = True
        'it would perform a task
        Exit Do
      End If
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
  
  If exists = False Then MsgBox "Something doesn't match"
End Sub

 

[dbkimber]

Ok, I've got this plugged in and the variables changed to match what I've named things. The good news is that I'm not getting any errors, the bad is that now matter what i enter in, I get the message that something doesn't match

 

[DanteAmor]

and the variables changed

What do you mean with that?

Did you try the macro with the data you gave as an example?

 

[dbkimber]

i changed the text boxes to the names to what i have them named.

if i click the button with out entering anything in the boxes it acts like everything works

 

[DanteAmor]

Did you change the name of the table and the fields?
You can put a sample of your data where you can see the names of the columns. Use xl2bb tool.
Also put here the macro you are using.