vba to check a pivot table for a value

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
Hello! I have a UserValue input through an InputBox. I would like to check a little one-column pivot table to see if that value exists in the table. If it does, I will pop up a message. If not, continue with the macro. This is what I wrote, but it's (obviously) not working. At the bolded line, I get the error "Object doesn't support this property or method." I was thinking of turning the pivot table into a range and checking to see if the value exists in the range. Would that be a better way?? Any suggestions are appreciated!

UserValue = InputBox("Enter the username you'd like to add here:")

If ThisWorkbook.Sheets("Inputs").ListObject(1).Value = UserValue Then
ans = MsgBox("The user " & UserValue & " was already added and will be added as a duplicate. Would you like to continue adding?", vbYesNoCancel)
Select Case ans
Case vbYes
ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue
Case vbNo
Exit Sub
Case vbCancel
Exit Sub
End Select
End If
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,108,646
Messages
5,524,060
Members
409,557
Latest member
Excelinho

This Week's Hot Topics

Top