Consulting

Results 1 to 7 of 7

Thread: Check if Value Exists in Another Sheet

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location

    Exclamation Check if Value Exists in Another Sheet

    Hello, how do I change the below code so when I enter a value on column M via paste it will return a messagebox saying "invalid data"?
    the below code works however it isn't limited to just column M. it happens to all cells

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim FindString As String
    Dim Rng As Range
    On Error Resume Next
    
    
    FindString = Range("M2").Value
    
    
    If Trim(FindString) <> "" Then
        With Sheets("IDs").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
                
            Else
                MsgBox "Invalid Data!" 'value not found
                Target.Clear
                
            End If
        End With
    End If
        
    End Sub
    Please help thank you
    Last edited by jazz2409; 06-01-2020 at 08:23 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •