Check table for value in TextBox and return value from same row in table

[K1600]

I'm sure this is quite simple and I am rather annoyed with myself that I can't get it to work but, I can't!

I have a table (Table9) in sheet (Trained Staff) which is formatted as an excel table with headers. The relevant of those headers for this bit being "Issuer" and "Authority status".

I also have a UserForm (UFAddEntry) with a text box (TxtIssuer) and I just want to look through the data in Table9 for the value in the textbox (in the Issuer column) and if the value in the 'Authority status' column is anything other than "Active" then to give me a messagebox with what the 'Authority status' value is.

Can anyone assist please. Thanks in advance.

 

[My Aswer Is This]

I'm going to assume that Listbox Authority status column is One columns to the Right of Issuer column in the Table

You will see offset(,1)
Which means one cells to the right of Issuer column.
If this is not true let me know:

Private Sub CommandButton1_Click()
'Modified  3/8/2021  12:00:04 AM  EDT
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = TxtIssuer.Value
Dim c As Long

With Sheets("Trained Staff").ListObjects("Table9")
    c = .ListColumns("Issuer").Index
End With

For Each r In Sheets("Trained Staff").ListObjects("Table9").ListColumns(c).Range
    If r.Value = ans Then If r.Offset(, 1).Value <> "Active" Then MsgBox r.Offset(, 1).Value, , "Authority status is:"
Next
Application.ScreenUpdating = True

End Sub

 

[K1600]

I'm going to assume that Listbox Authority status column is One columns to the Right of Issuer column in the Table

You will see offset(,1)
Which means one cells to the right of Issuer column.
If this is not true let me know:

Private Sub CommandButton1_Click()
'Modified  3/8/2021  12:00:04 AM  EDT
Application.ScreenUpdating = False
Dim r As Range
Dim ans As String
ans = TxtIssuer.Value
Dim c As Long

With Sheets("Trained Staff").ListObjects("Table9")
    c = .ListColumns("Issuer").Index
End With

For Each r In Sheets("Trained Staff").ListObjects("Table9").ListColumns(c).Range
    If r.Value = ans Then If r.Offset(, 1).Value <> "Active" Then MsgBox r.Offset(, 1).Value, , "Authority status is:"
Next
Application.ScreenUpdating = True

End Sub

So sorry for the delay, I didn't get an e-mail notification for some reason telling me someone had replied.

It wasn't in the next column however I have changed the offset to 4 and it seems to be working a treat, thanks for the help.

 

[My Aswer Is This]

So sorry for the delay, I didn't get an e-mail notification for some reason telling me someone had replied.

It wasn't in the next column however I have changed the offset to 4 and it seems to be working a treat, thanks for the help.

I did wonder why you had not responded.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
I always like figuring out ways to do thing. I did figure out a way to not use offset.
I will send you that code later and you might try it

 

[K1600]

I did wonder why you had not responded.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
I always like figuring out ways to do thing. I did figure out a way to not use offset.
I will send you that code later and you might try it

Yeah that would be great if you could.

Don't suppose it would be possible to also return a separate message box if the TxtIssuer value wasn't found is it? I tried to have a play with it this morning but couldn't get it to work.

Thanks again for your help.

 

[My Aswer Is This]

Yeah that would be great if you could.

Don't suppose it would be possible to also return a separate message box if the TxtIssuer value wasn't found is it? I tried to have a play with it this morning but couldn't get it to work.

Thanks again for your help.

Yes that is possible. What do you want the new message to say?

 

[K1600]

Yes that is possible. What do you want the new message to say?

Just something like "user not found".

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
'Modified  3/12/2021  2:09:33 AM  EDT
Dim ans As String
ans = TxtIssuer.Value
Application.ScreenUpdating = False
Dim found  As String
Dim r As Range
Dim c As Long
Dim cc As Long
Dim fr As Long
found = "No"

With Sheets("Trained Staff").ListObjects("Table9").DataBodyRange
    fr = Sheets("Trained Staff").ListObjects("Table9").Range.Cells(1, 1).Row
    c = Sheets("Trained Staff").ListObjects("Table9").ListColumns("Issuer").Index
    cc = Sheets("Trained Staff").ListObjects("Table9").ListColumns("Authority status").Index
    
    For Each r In Sheets("Trained Staff").ListObjects("Table9").ListColumns(c).Range
        If r.Value = ans Then
            If .Cells(r.Row - fr, cc).Value <> "Active" Then MsgBox .Cells(r.Row - fr, cc).Value, , "Authority staus is"
            found = "Yes"
        End If
     Next
End With
If found <> "Yes" Then MsgBox ans & "  Not found"
Application.ScreenUpdating = True
End Sub

 

[K1600]

Try this:

Private Sub CommandButton1_Click()
'Modified  3/12/2021  2:09:33 AM  EDT
Dim ans As String
ans = TxtIssuer.Value
Application.ScreenUpdating = False
Dim found  As String
Dim r As Range
Dim c As Long
Dim cc As Long
Dim fr As Long
found = "No"

With Sheets("Trained Staff").ListObjects("Table9").DataBodyRange
    fr = Sheets("Trained Staff").ListObjects("Table9").Range.Cells(1, 1).Row
    c = Sheets("Trained Staff").ListObjects("Table9").ListColumns("Issuer").Index
    cc = Sheets("Trained Staff").ListObjects("Table9").ListColumns("Authority status").Index
   
    For Each r In Sheets("Trained Staff").ListObjects("Table9").ListColumns(c).Range
        If r.Value = ans Then
            If .Cells(r.Row - fr, cc).Value <> "Active" Then MsgBox .Cells(r.Row - fr, cc).Value, , "Authority staus is"
            found = "Yes"
        End If
     Next
End With
If found <> "Yes" Then MsgBox ans & "  Not found"
Application.ScreenUpdating = True
End Sub

Thanks, I'm back in the office tomorrow so will give that a whirl then and let you know how it goes.

 

[My Aswer Is This]

Thanks, I'm back in the office tomorrow so will give that a whirl then and let you know how it goes.

Ok: