Excel 2010 - Userform - Updating data on same row on datasheet if same unique number

[TheShyButterfly]

Hi there,

I am just coming to grips with VBA and Userforms and have been researching extensively on the Web for any existing code that I could use, but have been unsuccessful thus far.

I am in the process of developing an Excel 2010 Userform which will be used to record Incidents in Excel 2010. <o></o>
The name of the workbook is SWIRL-SECINC Instrument.xlsm

The worksheet name where I am storing the data from the Userform is called IncidentData (sheet2). The Userform name is Security_Incident_Form

Each Incident has a unique number, eg: INC-25450, etc. (the unique number is not automatically generated via excel, this comes from an external source)

I have Multipages, combo boxes, etc

Some of the fields are: <o></o>
Incident Number, Incident Date, Logged by, Address, Incident Type, What happened, Root Cause, Vehicle Information, Closing Comments, etc…. (there will be in excess of 200 fields/colums of data.


The problem is that not all the fields are completed in the initial entry … the userform will be updated at a later stage when all the details have been collected that the rest of the fields are then to be completed and the data saved to the same row that the initial data was entered overwriting ALL the columns of the spreadsheet for that unique Incident number).

I have started to code VBA to transfer the data into a worksheet named IncidentData but am wondering how to code to get it to check if the Incident number exists, and if Yes, then I want VBA to populate all the fields of the Userform to the incidentdata (sheet2) datasheet.

This is the code I currently have that I obtained from the web, and it did work, but for some reason it no longer works, I tried Dim eRow but then gives me errors ... it worked before without the Dim. (I think I've been trying too hard and my brain has turned to mush )

I am currently getting an error on this line " eRow = IncidentData.Cells(Row.Count, 1).End(x1Up).Offset(1, 0).Row "<o></o>

Private Sub CMDB_TransferToDataSheet_Click()
​

'the CMDB is the ActiveX Button.

'erow finds the next empty Row
'Once if it is empty, then it will go to the 1st empty row and add
'in the details from the particular field on the form into the datasheet.

'Example:
'1,2,3 etc below, are referring to which columns the data has to go
'if the row doesn't contain any data

eRow = IncidentData.Cells(Row.Count, 1).End(x1Up).Offset(1, 0).Row
Cells(eRow, 1).Value = TXT_SecurityIncidentNo
Cells(eRow, 2).Value = CMBX_Status
Cells(eRow, 3).Value = CMBX_AllocatedTo
'Cells(erow, 4) ... etc (in excess of 200 columns).Value =<o></o>
​

End Sub

​

The aim of the transfer of data to the IncidentData sheet is, If there is no match for the unique incident number, then it needs to create a new entry on the next empty row of the incidentdata sheet.


Just as additional information, that may be useful ... I am developing the Userform at home on Windows 8 64bit PC, but at work I am currently using Windows XP, which is later going to be upgraded to windows 7 32bit.


<o></o>
Do you require any further information?<o></o>
I thank you in advance for your time and effort. <o></o>
Cheers,<o></o>
TheShyButterfly<o></o>

 

[dmt32]

Hi,
not tested but see if this admendment to your code helps you.

Private Sub CMDB_TransferToDataSheet_Click()
    Dim wsIncidentData As Worksheet
    Dim FoundCell As Range
    Dim Search As String
    Dim eRow As Long
    
    Set wsIncidentData = Worksheets("IncidentData")
    'check if incidentNo exists
    Search = TXT_SecurityIncidentNo.Text
    With wsIncidentData
        Set FoundCell = .Columns(1).Find(Search, LookIn:=xlValues, LookAt:=xlWhole)
        If Not FoundCell Is Nothing Then
            'incidentNo exists
            eRow = FoundCell.Row
        Else
            'new record
            eRow = .Cells(.Rows.Count, 1).End(x1Up).Offset(1, 0).Row
        End If
        
        .Cells(eRow, 1).Value = TXT_SecurityIncidentNo
        .Cells(eRow, 2).Value = CMBX_Status
        .Cells(eRow, 3).Value = CMBX_AllocatedTo
        'Cells(erow, 4) ... etc (in excess of 200 columns).Value =
    End With
End Sub

Dave

 

[dmt32]

sorry, did not think about way you were obtaining new row with your code - which as written, it will cause an error:

you need to change this line:

eRow = .Cells(.Rows.Count, 1).End(x1Up).Offset(1, 0).Row

to this:

eRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

Dave

 

[TheShyButterfly]

Thank you so much Dave.

I copied your code and over wrote my code .... ran the form, and I'm sorry to report, but nothing happened.
I entered data via the form, deliberately leaving out some data fields, with the intention of using the same Incident number and completing those fields, but .... no data appeared in the IncidentData columns.

I get a debug 'run-time error '1004' - application-defined or object-defined error' after ('new record)
eRow = .Cells(.Rows.Count, 1).End(x1Up).Offset(1, 0).Row

I thought if I entered the IncidentData after eRow=IncidentData.cells(.Rows.Count, 1).End(x1Up).Offset(1, 0).Row
that got rid of the '1004' error, but replaced it with another run-time error: '424' Object required.

Perhaps this '424' error is referring to the code eRow=IncidentData.cells(.Rows.Count, 1).End(x1Up).Offset(1, 0).Row

I have deleted row 1 in the IncidentData sheet in case that was causing the hiccup (which was a merged row), but makes no difference, I still get the runtime error: '424'.

But I do appreciate the effort you've put in ....

Cheers,
Ingelise

 

[TheShyButterfly]

HOLD OFF Dave ... !!!!
I was SO excited to test your handiwork that I didn't 'register' your second post .... DOH!

I will amend the code to what you specified below and I will test it out over this weekend. I appologise for jumping ahead of myself and will take greater care to READ and APPLY all posts before I respond

Have a great weekend and I hope that I'll be able to report back in the 'positive'

Thanking you again for your patience and your time and effort.
cheers,
Ingelise

sorry, did not think about way you were obtaining new row with your code - which as written, it will cause an error:

you need to change this line:

eRow = .Cells(.Rows.Count, 1).End(x1Up).Offset(1, 0).Row

to this:

eRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

Dave