VBA code for message box if data already exist.

[Master_Viper]

Hello Expert,

Please help me create vba code that message box will popup when data already exist in another sheet.

I have a command button in sheet 1 and when I click this the data will automatically copied/transfer to sheet 2 and when the data already exist a message popup will diplay with "Yes" or "No" button.

E.g: What if you want to compare the data entered into column A on "sheet 1" against the data in column A on "sheet 2"?

[bakerman2]

Not much info to go on but Find or Application.Match will probably do the trick.
Best to post example file with code you created for commandbutton.

[Master_Viper]

Hello, bakerman2,

What I want to do is when I clicked the Update Data button in Data_Entry and the data already exist in ER100_Activation_Configuration sheet a message box should be displayed.
I want to check if the unique data under column E Row 19 of Data_Entry sheet was already exist.

See attach file.

[bakerman2]

Number in column D is the same but format on Data Entry is different. What about that?

[Master_Viper]

Hello Expert,

The file attached has been updated same format with ER100_Configuration_Activation sheet. Like what I have said in the previous post. I want to check if the data from Data_Entry sheet under column D row 19 is already exist in the ER100_Activation_Configuration sheet under column D. If that the case a message box popup will appear/display.

Thanks in advance for the help.

[bakerman2]

Try this.

Please Login or Register  to view this content.

[Master_Viper]

Hello bakerman2,

I got an error below in yellow highlighted font. Run-time error '13': Type mismatch.

Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, a As String, b As String, c As String
Dim fRow As Long
Ln = 7
Set sh1 = Sheets("Data_Entry")
fRow = Application.Match(sh1.Range("E19"), Sheets("ER100_Activation_Configuration").Columns(4), 0)
If Not IsError(fRow) Then MsgBox "Number already present": Exit Sub
x = Mid(sh1.Range("E431"), 5, Ln)
With Range("E431")
.Value = x
.NumberFormat = WorksheetFunction.Rept("0", Ln)
End With
Set sh1 = Sheets("Data_Entry")
If sh1.Range("S448") = "N" Or sh1.Range("S448") = "ONT" Or sh1.Range("S448") = "PON" Then
b = "3Spring"
Else: sh1.Range("S448") = "Nest3"
b = "4Spring"
End If
If sh1.Range("E448") = "2858097400100" Then
c = "PS100"
Else: sh1.Range("E448") = "2858041501100"
c = "PS60"
End If
a = Array(sh1.Range("E19").Value, sh1.Range("E9").Value, sh1.Range("E7").Value, Mid(sh1.Range("E7"), 10, 4), sh1.Range("M446").Value, sh1.Range("O9").Value, _
sh1.Range("E434").Value, Mid(sh1.Range("E434"), 5, 7), sh1.Range("S444").Value, sh1.Range("E440").Value, Mid(sh1.Range("E440"), 5, 7), _
sh1.Range("E448").Value, c, sh1.Range("S448").Value, b)
With Sheets("ER100_Activation_Configuration").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 21).Value = a
.Resize(, 21).NumberFormat = "0"
.Offset(, -2).Value = .Row() - 2
.Offset(, -1).Value = Format(Now(), "mm-dd-yy")
End With
End Sub

[bakerman2]

Format Data_Entry E19 as Number like it is on ER100_Activation_Configuration Column D.

[Master_Viper]

Hello bakerman2,

Follow your instruction above but still same error occured. ANy help on this.

[bakerman2]

See example file.

[Master_Viper]

bakerman2 tried the sample above it's working when click the update button in the Data_Entry with the same number in the column E row 19, but when I change any number in the same column still run-time 13 mismatch appear.

[bakerman2]

Try this.

Please Login or Register  to view this content.

[Master_Viper]

bakerman2 still unluckily the message box did not popup when there was data from ER100_Activation_Configuration sheets.

[bakerman2]

I'm sorry but I'm done here. For me it runs like clockwork.
When number exists msgbox pops up, when new number all data gets copied to other sheet.
There's nothing more I can do.

[Master_Viper]

Anyone can debug the code together with the sample file.

Thanks..

[bakerman2]

What 's there to debug, it runs just fine.