VBA question, how to create a warning if inputted data exists on separate data sheet

jsmith2094

New Member
Joined
Aug 19, 2021
Messages
35
Office Version
  1. 365
Platform
  1. Windows
HI,

I have a spreadsheet which allows users to input data on Sheet1 and then when they press submit it transfers the data to a database sheet called Sheet2

that part works fine but I need to have a macro to check Sheet2 first to see if any of the data from Sheet 1 has already been submitted and if it has to display a message to say this data has already been submitted and then for the macro to stop as I have 2 other macros running.

this is what I have so far but it only displays a message if the user does not input anything, if the user inputs data it continues to run without a warning if the data is already on Sheet 2

also

On Sheet 1 the data is inputted into column D rows 13 to 16 but on the below macro if I want to check multiple cells such as FindWhat = Worksheets("Sheet1").Range("D13:D16").Value

it gives me a runtime error.

VBA Code:
Sub Procedure1()

Dim FoundCell As Range
Dim FindWhat As String
    FindWhat = Worksheets("Sheet1").Range("D13").Value
    Set FoundCell = Worksheets("Sheet2").Range("A:A").Find(What:=FindWhat, LookAt:=xlWhole)
    
    If Not FoundCell Is Nothing Then
        MsgBox "Found " & FindWhat & " Data already Submitted to the Fraud Team!"
        Exit Sub
    End If
    
End Sub

any suggestions?
 
thanks @dmt32 , I tried your code but still get the same problem as what joe4 mentioned.

when I run the code I get the error saying the data exists on sheet2 when it doesn't and the rest of the subs wont run
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I tried the above code you mentioned and cleared the contents of sheet2 , when I run the macro it gives me the warning message saying the data exists on sheet 2 and the macro stops but the Sheet2 has nothing in it so im not sure why its not running the rest of the macros.

I only need it to stop if the data does exist on sheet2 , if it is new data it should run procedure 2 and 3 as normal?
Do you have any blanks in cells D13:D16?
If so, it is looking for a blank, and finding them in ALL cells on Sheet2!!!

If you want to skip any "blanks" found in cells D13:D16, you should update that procedure like this:
VBA Code:
Sub Procedure1()

    Dim FoundCell As Range
    Dim FindWhat As String
    Dim cell As Range

    For Each cell In Worksheets("Sheet1").Range("D13:D16")
        If cell <> "" Then
            FindWhat = cell.Value
            Set FoundCell = Worksheets("Sheet2").Range("A:A").Find(What:=FindWhat, LookAt:=xlWhole)
   
            If Not FoundCell Is Nothing Then
                MsgBox "Found " & FindWhat & " Data already Submitted to the Fraud Team!"
                Exit Sub
            End If
        End If
    Next cell
   
'   Set RunProcs to Yes
    RunProcs = "Yes"
   
End Sub
 
Upvote 0
thanks @dmt32 , I tried your code but still get the same problem as what joe4 mentioned.

when I run the code I get the error saying the data exists on sheet2 when it doesn't and the rest of the subs wont run

That suggests you may have blank cells in the range

Try this update to function

VBA Code:
Function Procedure1() As Boolean
   
    Dim FoundCell   As Range, cell As Range
    Dim FindWhat    As String
   
    For Each cell In Worksheets("Sheet1").Range("D13:D16")
        FindWhat = cell.Value
        If Len(FindWhat) > 0 Then
            Set FoundCell = Worksheets("Sheet2").Range("A:A").Find(What:=FindWhat, LookAt:=xlWhole)
           
            If Not FoundCell Is Nothing Then
                MsgBox "Found " & FindWhat & Chr(10) & " Data already Submitted To the Fraud Team!", 48, "Record Exists"
                Procedure1 = True
                Exit For
            End If
        End If
    Next cell
   
End Function

Dave
 
Upvote 0
@Joe4

I tried the updated code but now when run the macro it runs fine the first time but when I run it again with the same information in Cells D:13 - D:16 it should now give the error to say that the same information exists on sheet2 as the data is already on Sheet2 but it doesn't give the error and still runs the rest of the subs.

On Sheet2 all of the data is held in Colum A
 
Upvote 0
I forgot that the Global Variable is not reset in between runs, only between Excel sessions.
Just add this one line and it should fix that:
Rich (BB code):
Sub Procedure1()

    Dim FoundCell As Range
    Dim FindWhat As String
    Dim cell As Range

    For Each cell In Worksheets("Sheet1").Range("D13:D16")
        If cell <> "" Then
            FindWhat = cell.Value
            Set FoundCell = Worksheets("Sheet2").Range("A:A").Find(What:=FindWhat, LookAt:=xlWhole)
  
            If Not FoundCell Is Nothing Then
                MsgBox "Found " & FindWhat & " Data already Submitted to the Fraud Team!"
                RunProcs = "No"
                Exit Sub
            End If
        End If
    Next cell
  
'   Set RunProcs to Yes
    RunProcs = "Yes"
  
End Sub
 
Upvote 0
Solution
@Joe4

I tried your updated code and when I run it it still don't give me the warning message when the data exists on sheet2 it just proceeds to run the rest of the subs
 
Upvote 0
I tried your updated code and when I run it it still don't give me the warning message when the data exists on sheet2 it just proceeds to run the rest of the subs
I tested it out, and it works for me.
Note that the data needs to match EXACTLY.

If it does not work, post a real simple example of one not working, letting me exactly what data you have in D13:D16 on sheet1, and let me know what cell on Sheet2 that the matching data exists in.
 
Upvote 0
@Joe4

Your code did work lol , thank you so much for your help. I really apricate it

the problem was the data going in sheet2 was merging the cells A to D so when I updated it to A:D it works perfect :)
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top