Storing data from user form to excel

[krlmlt89]

I have a user form with a few test boxes where the user will input data. Each text box will store the data in a particular row on a worksheet once the user clicks the ADD button.

The code I am using for storing the data is as follows:

ActiveSheet.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Me.NCPNo.Value
Worksheets("ANNUAL NCP DATA").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Me.NCPNo.Value

ActiveSheet.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Me.IssueDate.Value
Worksheets("ANNUAL NCP DATA").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Me.IssueDate.Value

ActiveSheet.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Me.IssueMonth.Value
Worksheets("ANNUAL NCP DATA").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Me.IssueMonth.Value

The problem with this code is that if for example in one of the entries which will be stored in row A, text box 1 is intentionally left blank, then the next entry will store the data from text box 1 in row A instead of in row B.

Any idea how I can alter the code above so that each entry will fill up one row and shift storage of data to the next row, thus ignoring blank entries?

Thanks.

[OllieB]

Replace the code shown with

     Dim lngRowForDataEntry As Long

     With ThisWorkbook.ActiveSheet
          lngRowForDataEntry = .UsedRange.Rows.Count+1
          .Cells(lngRowForDataEntry, "B").Value = Me.NCPNo.Value
          .Cells(lngRowForDataEntry, "C").Value = Me.IssueDate.Value
          .Cells(lngRowForDataEntry, "D").Value = Me.IssueMonth.Value
     End With

     With ThisWorkbook.Worksheets("ANNUAL NCP DATA")
          lngRowForDataEntry = .UsedRange.Rows.Count+1
          .Cells(lngRowForDataEntry, "B").Value = Me.NCPNo.Value
          .Cells(lngRowForDataEntry, "C").Value = Me.IssueDate.Value
          .Cells(lngRowForDataEntry, "D").Value = Me.IssueMonth.Value
     End With

[krlmlt89]

For some reason no data is being stored when using this code...any idea why?

[OllieB]

The data should be stored. The only thing I can think of is that it may be placed on a row you do not expect (further down in the worksheet), because of the value returned for .UsedRange.Rows.Count. Can you debug what value is assigned to variable lngRowForDataEntry?

[krlmlt89]

How can I assign it to start inputting at the next empty row? It stored the data in row 5663 for some reason!

[OllieB]

There are probably a lot of empty rows in the various worksheets. I suggest you manually delete those empty rows, close and re-open the workbook and your problem should be solved.

[krlmlt89]

I tried doing what you told me but to no avail as the problem still persists...the data is still being starting to be stored at a random row.

[OllieB]

The instruction worksheets("name").UsedRange.Rows.Count normally always returns the last row used in the worksheet, unless there are a lot of empty rows which have been used in the past or never cleared up.

It is basically the same as pressing the Ctl-End key at the same time, to move to the last cell used.

As you will have empty values in columns A, B and C I cannot think of another way of determining the last row.

Deleting empty rows, closing the workbook and re-opening, should do the trick.

Can you post a copy of your workbook?

[krlmlt89]

Attached!

PLease forward any solution if possible.

Thanks

[OllieB]

Please accept the revised workbook attached. I have removed the empty rows from worksheets sheet1 and ANNUAL.. There was a macro error on opening of the workbook (worksheet HOME not found) which MAY have interfered with Excel properly updating the used ranges, but that is just speculation from my side.

[krlmlt89]

Yes i had removed the home worksheet before sending it to you...what did you do to empty the rows please?

[OllieB]

I just deleted the rows (select & delete), saved and re-opened the workbook. Like I said this trick only worked after I had commented out the incorrect statement in the workbook_open routine.

[krlmlt89]

Ok thanks for your help and patience

[OllieB]

You are very welcome. Remember to click the star when you are happy with my contribution