Check if value exists in column in VBA

Question

I have a column of numbers of over 500 rows  I need to use VBA to check if variable X matches any of the values in the column   Can someone please help me

User · Accepted Answer

If you want to do this without VBA  you can use a combination of IF  ISERROR  and MATCH   So if all values are in column A  enter this formula in column B    IF ISERROR MATCH 12345 A A 0    Not Found   Value found on row    amp  MATCH 12345 A A 0     This will look for the value  12345   which can also be a cell reference   If the value isn t found  MATCH returns   N A  and ISERROR tries to catch that   If you want to use VBA  the quickest way is to use a FOR loop   Sub FindMatchingValue       Dim i as Integer  intValueToFind as integer     intValueToFind   12345     For i   1 to 500      Revise the 500 to include all of your values         If Cells i 1  Value   intValueToFind then              MsgBox  Found value on row    amp  i              Exit Sub         End If     Next i        This MsgBox will only show if the loop completes with no success     MsgBox  Value not found in the range      End Sub   You can use Worksheet Functions in VBA  but they re picky and sometimes throw nonsensical errors  The FOR loop is pretty foolproof

User · Answer

Just to modify scott s answer to make it a function   Function FindFirstInRange FindString As String  RngIn As Range  Optional UseCase As Boolean   True  Optional UseWhole As Boolean   True  As Variant      Dim LookAtWhat As Integer      If UseWhole Then LookAtWhat   xlWhole Else LookAtWhat   xlPart      With RngIn         Set FindFirstInRange    Find What  FindString                                         After   Cells  Cells Count                                          LookIn  xlValues                                         LookAt  LookAtWhat                                         SearchOrder  xlByRows                                         SearchDirection  xlNext                                         MatchCase  UseCase           If FindFirstInRange Is Nothing Then FindFirstInRange   False      End With  End Function   This returns FALSE if the value isn t found  and if it s found  it returns the range   You can optionally tell it to be case-sensitive  and or to allow partial-word matches   I took out the TRIM because you can add that beforehand if you want to     An example   MsgBox FindFirstInRange StringToFind  Range  2 2    TRUE  FALSE  Address   That does a case-sensitive  partial-word search on the 2nd row and displays a box with the address   The following is the same search  but a whole-word search that is not case-sensitive   MsgBox FindFirstInRange StringToFind  Range  2 2    Address   You can easily tweak this function to your liking or change it from a Variant to to a boolean  or whatever  to speed it up a little   Do note that VBA s Find is sometimes slower than other methods like brute-force looping or Match  so don t assume that it s the fastest just because it s native to VBA   It s more complicated and flexible  which also can make it not always as efficient   And it has some funny quirks to look out for  like the  Object variable or with block variable not set  error

User · Answer

The find method of a range is faster than using a for loop to loop through all the cells manually   here is an example of using the find method in vba  Sub Find First   Dim FindString As String Dim Rng As Range FindString   InputBox  Enter a Search value   If Trim FindString   lt  gt     Then     With Sheets  Sheet1   Range  A A    searches all of column A         Set Rng    Find What  FindString                            After   Cells  Cells Count                             LookIn  xlValues                            LookAt  xlWhole                            SearchOrder  xlByRows                            SearchDirection  xlNext                            MatchCase  False          If Not Rng Is Nothing Then             Application Goto Rng  True  value found         Else             MsgBox  Nothing found   value not found         End If     End With End If End Sub

User · Answer

Simplest is to use Match  If Not IsError Application Match ValueToSearchFor  RangeToSearchIn  0   Then       String is in range

User · Answer

Try adding WorksheetFunction   If Not IsError Application WorksheetFunction Match ValueToSearchFor  RangeToSearchIn  0   Then   String is in range

User · Answer

try this   If Application WorksheetFunction CountIf RangeToSearchIn  ValueToSearchFor    0 Then Debug Print  none  End If

[excel] Check if value exists in column in VBA

Examples related to excel

Examples related to vba

Examples related to matching