The data inserted is repeating itself in spreadsheet

Question

Mireya B. 0 Newbie Poster

11 Years Ago

Hello, I am using VBA userform in MS Excel to insert data for database purpose. When I tested it for the first time, it was running okay. But after I saved it and used it again to insert the real data, it happens to repeat/duplicate the data to multiple times (up to 5 rows) in the spreadsheet.

Here are the codes I am using to export the data from userform to spreadsheet.

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet3 Active
If UnplannedCategoryComboBox.Value = "Anti-Surge Valve System (AVS)" Then Sheets(3).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value

If UnplannedYearComboBox.Value = "2002" Then Cells(emptyRow, 3).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2003" Then Cells(emptyRow, 4).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2004" Then Cells(emptyRow, 5).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2005" Then Cells(emptyRow, 6).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2006" Then Cells(emptyRow, 7).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2007" Then Cells(emptyRow, 8).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2008" Then Cells(emptyRow, 9).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2009" Then Cells(emptyRow, 10).Value = UnplannedDurationTextBox.Value

Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

'Make Sheet4 Active
If UnplannedCategoryComboBox.Value = "Centrifugal Gas Compressor (GC)" Then Sheets(4).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value

If UnplannedYearComboBox.Value = "2002" Then Cells(emptyRow, 3).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2003" Then Cells(emptyRow, 4).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2004" Then Cells(emptyRow, 5).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2005" Then Cells(emptyRow, 6).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2006" Then Cells(emptyRow, 7).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2007" Then Cells(emptyRow, 8).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2008" Then Cells(emptyRow, 9).Value = UnplannedDurationTextBox.Value

If UnplannedYearComboBox.Value = "2009" Then Cells(emptyRow, 10).Value = UnplannedDurationTextBox.Value

Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

End Sub

So, the question is, how do I prevent the data from duplicating itself in the spreadsheet? Where did I do wrong in the coding? Please help me.

Thank you in advance.

microsoft-office vb.net visual-basic

Edited 11 Years Ago by Mireya B.

3 Contributors
8 Replies
319 Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by Stuugie

Begginnerdev 256 Junior Poster

11 Years Ago

One question would be - What differs from test data to live data.

Does the data format change, or just values?

Stuugie 50 Marketing Strategist

11 Years Ago

I'm always in favour of explicitly setting ranges and cells to the worksheets they are supposed to update. When you just use Cells(blah), if the worksheet changes then the new worksheet will change. It also allows for intellisense, which I really like and find very convenient.

Now, I don't know if this corrects your issues because I cannot test this and as Begginnerdev points out, the problem might be with your data so it's tought to say but let me know if the following works or not

Private Sub OKButton_Click()

    Dim ws3 As Worksheet, ws4 As Worksheet
    Dim emptyRow As Long
    Dim x As Long

    Set ws3 = Sheets(3)
    Set ws4 = Sheets(4)
    If UnplannedCategoryComboBox.Value = "Anti-Surge Valve System (AVS)" Then
        emptyRow = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws3
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws3.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws3.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value
    If UnplannedCategoryComboBox.Value = "Centrifugal Gas Compressor (GC)" Then
        emptyRow = ws4.Cells(ws4.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws4
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws4.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
        ws4.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value
    End If

End Sub

Begginnerdev commented: Nice code snippet. +8

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Mireya B. 0 Newbie Poster · Answer 1 · 2013-07-06T06:18:49+00:00

To Begginnerdev,
the data changes only for the values, the format stays the same. I use random data for testing just to see that the data is exported correctly to the intended worksheets and on the rows that I wanted.

To Stuugie,
Thanks for the coding! I applied it for the rest of 10 worksheets that I have but it only works for the first ones. When I tried to insert the data for the next worksheets, it says error 1004, application-defined or object-defined error. What was that supposed to mean?

Here is your coding that I used for my 10 worksheets.

Private Sub OKButton_Click()
    Dim ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet, ws7 As Worksheet, ws8 As Worksheet, ws9 As Worksheet, ws10 As Worksheet, ws11 As Worksheet, ws12 As Worksheet
    Dim emptyRow As Long
    Dim x As Long
    Set ws3 = Sheets(3)
    Set ws4 = Sheets(4)
    Set ws5 = Sheets(5)
    Set ws6 = Sheets(6)
    Set ws7 = Sheets(7)
    Set ws8 = Sheets(8)
    Set ws9 = Sheets(9)
    Set ws10 = Sheets(10)
    Set ws11 = Sheets(11)
    Set ws12 = Sheets(12)

    If UnplannedCategoryComboBox.Value = "Anti-Surge Valve System (AVS)" Then
        emptyRow = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws3
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws3.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws3.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Centrifugal Gas Compressor (GC)" Then
        emptyRow = ws4.Cells(ws4.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws4
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws4.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws4.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Fuel System (FS)" Then
        emptyRow = ws5.Cells(ws5.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws5
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws5.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws5.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Gearbox (GB)" Then
        emptyRow = ws6.Cells(ws6.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws6
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws6.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws6.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Gas Turbine (GT)" Then
        emptyRow = ws7.Cells(ws7.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws7
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws7.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws7.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Lube Oil System (LOS)" Then
        emptyRow = ws8.Cells(ws8.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws8
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws8.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws8.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Process & Utilities (PRO)" Then
        emptyRow = ws9.Cells(ws9.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws9
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws9.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws9.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Starter System (SS)" Then
        emptyRow = ws10.Cells(ws10.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws10
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws10.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws10.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Turbine Control System (TCS)" Then
        emptyRow = ws11.Cells(ws11.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws11
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws11.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
    End If
    ws11.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value

    If UnplannedCategoryComboBox.Value = "Vibration Monitoring System (VMS)" Then
        emptyRow = ws12.Cells(ws12.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws12
            .Cells(emptyRow, 1).Value = UnplannedDateTextBox.Value
            .Cells(emptyRow, 2).Value = UnplannedMonthComboBox.Value
        End With
        For x = 2 To 9
            If UnplannedYearComboBox.Value = "200" & x Then ws12.Cells(emptyRow, x + 1).Value = UnplannedDurationTextBox.Value
        Next x
        ws12.Cells(emptyRow, 11).Value = UnplannedCauseFailureTextBox.Value
    End If

End Sub

Stuugie 50 Marketing Strategist · Answer 2 · 2013-07-06T12:54:10+00:00

Hi Mireya, can you zip your file and upload it? I can mess around with your code but I cannot test it at all which makes it difficult to accurately tell how to fix the errors. I'll try if I have to but it would be so much easier on me to work with your file.

Mireya B. 0 Newbie Poster · Answer 3 · 2013-07-06T14:56:52+00:00

Mireya B. 0 Newbie Poster

11 Years Ago

Hi Stuugie, here are two files - one with the new codes that u've given me, and the other is the original ones. Thank you in advance!

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

MAT_USD_-_new_codes.zip (297.07 KB)

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

MAT_USD_-_original.zip (296.58 KB)

Stuugie 50 Marketing Strategist · Answer 4 · 2013-07-07T13:04:24+00:00

Hi Mireya B.,

Try the following workbook and let me know if it more suits your requirements. There were chances of double entries because if the user hit enter button more than once the same data in the form would be entered twice (or however many times that button would be accidentally pressed. I've added a bit of code to remove the fields after that button is pressed, just as a precaution. At any rate, I think it is working properly now.

Mireya B. 0 Newbie Poster · Answer 5 · 2013-07-07T13:53:30+00:00

Hi Stuugie,
The codes are working perfectly, and it suits my requirements. I'm so glad and thank you so much for your help.

Stuugie 50 Marketing Strategist · Answer 6 · 2013-07-07T14:46:24+00:00

You are very welcome, please don't forget to mark this thread as solved.