Validate if a value is already in a column from another sheet-CodePudding

Hi ive tried to create a code so the user from cant insert the same id in the table, so ive created an insert button that validates it before he actually inserts something , but for some reason it isnt working, please help me(im new to VBA):

Heres my code:

Sub Insert()

'
' insert Macro
'
Dim row As Double, search As String
row = 5
search = ""

Do

row = row   1

  If Sheet1.Cells(17, 9).Value = Data.Cells(row, 1).Value Then GoTo Error
  If Sheet1.Cells(17, 9).Value <> Data.Cells(row, 1).Value Then GoTo Insert
   Exit Do
 
Loop Until Data.Cells(row, 1).Value = ""


Error:
    search = "Ok"
     MsgBox "Id already exists", vbExclamation,"Error"
  Exit Sub
  
Insert:

 If IsEmpty(Range("I17")) Or IsEmpty(Range("D11")) and search ="" Then
    MsgBox "all the fields must be filled", vbExclamation
  Else:
  ' heres the  code to insert values from sheet1  to sheet2
  
  End If
  Exit Sub

For some reason its not looping its only validating if the value found in the first row of the table from the other sheet(Data), which is the row 6. Both cell from sheet1 and the column from Data are converted to Numbers Format(exactly the same).

Heres the Id the user insert which im trying to validate ID being I17 cell(sheet name being Form):

Ps: if u need more info let me know

And here is the field im trying to lookin if the value matchs(Data Sheet):

CodePudding user response：

Because I do not know exactly what you are trying to do, this is the simplest way to get you started.

Sub Insert()
'
' insert Macro
'
    Dim row As Double, search As String
    row = 5
    search = ""

Do
    row = row   1

    If Sheet1.Cells(17, 9).Value = Data.Cells(row, 1).Value Then
        'What to do if the value matched Goes Here
        MsgBox "Id already exists on Data in Row: " & row, vbExclamation,"Error"
        Exit Sub 'if you want to exit when the value was found
    Else
        'You want to leave this empty and do what you want to do after the loop is done
        'and a match was not found
    End If
Loop Until Data.Cells(row, 1).Value = ""
'End of the loop.
'If the Sub gets to this point it means that the Value was not found at all after looping all the data.
'Enter below what to do if the value was not found
Data.Cells(row, 1).Value = Sheet1.Cells(17, 9).Value
Data.Cells(row, 2).Value = Sheet1.Cells(17, 12).Value 'Which Column has the name? I guessed 12

Exit Sub

I'm guessing this will work, however this is a very bad way to look for a value, instead you could use Data.Columns("A:A").Find(What:=Sheet1.Cells(17,9).Value) and you dont have to loop a thing, I recommend doing a little reading/google searching on that approach before trying it that way.