Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA macro to popup message box if data already exist

    Hello Expert,

    I would like to seek help. Here's the scenario.

    1. I have two sheets the first sheet contains all the data with button. When I click the button it should be copied automatically to sheet 2 but if sheet 2 contain already the data
    a message box will popup with vbYesNo button that the statement should be " Found same data. You want to replace?". And when I click the "Yes" the data from sheet 1 will automatically copied to sheet 2
    but it replaced the data. If "No" button press it will exit/quit nothing will happen.

    2. The data which I would like to compare is under column E and row 19 of shett Data_Entry and compare to ER100_Activation_Configuration under column D. Please seesample below for your reference.

    Thanks for the help.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    To All: Code evaluated in this workbook is safe

    Viper,

    Very nice coding (kudos)! To act on the Y/N response of the MsgBox, add code highlighted in blue

    Code:
    Private Sub CommandButton1_Click()
        Dim sh1 As Worksheet, a, b, c As String
        Dim Found As Range, Choice As Integer
         
         
        Set Found = Find_All(Range("E19"), Worksheets("ER100_Activation_Configuration").Range("D11:D100"), , xlWhole)
        If Not Found Is Nothing Then
            Choice = MsgBox("File already exist. Do you want to replace?", vbYesNo, "Found SEMI PN")
            If Choice = 7 Then Exit Sub
        'MsgBox "SEMI PN already exist. - " & Range("E19")
        End If
        Ln = 7
        '......REST OF CODE
    HTH,
    Maud

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    And when I click the "Yes" the data from sheet 1 will automatically copied to sheet 2 but it replaced the data.
    Viper,

    To complete the second part of your request, make the following changes highlighted in blue. In the ER100_ACTIVATION_CONFIGURATION spreadsheet Cell P13, I change the initial value to PS60XXXXX so you will be able to see the record has been changed after running the code. Also added dummy SEMI PNs in column D to show that the replaced row does not need to be the last record listed.

    HTH,
    Maud

    Code:
    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 Found
        '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() - 1  'CHANGED ROW OFFSET FROM -2 TO -1
            .Offset(, -1).Value = Format(Now(), "mm-dd-yy")
        End With
    End Sub
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Jul 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maudibe,

    Tried the code above it will replaced if data already exist, but when I have new data under Data_Entry I got an error of
    Run-time error "91": Object variable or without block variable not set and it highlighted the .Resize(, 21).Value = "0"

    Thanks again!

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Viper,

    Modify the code to the following:

    Code:
        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)
        If Not Found Is Nothing Then 'MATCHING RECORD
            With Found
                .Resize(, 21).Value = a
                .Resize(, 21).NumberFormat = "0"
                .Offset(, -2).Value = .Row() - 1
                .Offset(, -1).Value = Format(Now(), "mm-dd-yy")
            End With
        Else: 'NEW RECORD
            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() - 1
                .Offset(, -1).Value = Format(Now(), "mm-dd-yy")
            End With
        End If
    End Sub
    Attached Files Attached Files

Posting Permissions

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