• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UserForm not adding data to table appropriately

Rachel B

New Member
Hi all,

I'm new to VBA. By looking at a lot of online examples, I've managed to create a UserForm to input data into an Excel table. When I click the "Add" button on the userform, I want it to add a new row to the bottom of the table (above the Totals line) and input the data entered in the userform into that new, blank row. The code that I've written inserts the new row correctly but, instead of putting the data from the userform in that row, it overwrites the last row of the table that already contains data. If I click the "Add" button a second time, it enters a second blank row but overwrites the same line of data.

My code looks like this...
Code:
Private Sub cmdAdd_Click()
Dim emptyRow As Long

    'Make Production Schedule sheet active
    Sheet1.Activate
   
    'Add new row
    Call AddRow
   
    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
   
    'Transfer information
    Cells(emptyRow, 1).Value = txtCustomer.Value
    Cells(emptyRow, 2).Value = txtOrder.Value
    Cells(emptyRow, 3).Value = txtDateRecd.Value
    Cells(emptyRow, 4).Value = txtDateReqd.Value
    Cells(emptyRow, 5).Value = ""
    Cells(emptyRow, 6).Value = ""
    Cells(emptyRow, 9).Value = txtBillet.Value
    Cells(emptyRow, 10).Value = ""
    Cells(emptyRow, 12).Value = txtSlab.Value
    Cells(emptyRow, 13).Value = ""
    Cells(emptyRow, 15).Value = txtChip.Value
    Cells(emptyRow, 16).Value = ""
    Cells(emptyRow, 18).Value = txtFlame.Value
    Cells(emptyRow, 19).Value = ""
    Cells(emptyRow, 21).Value = txtSandblast.Value
    Cells(emptyRow, 22).Value = ""
    Cells(emptyRow, 24).Value = txtCut.Value
    Cells(emptyRow, 25).Value = ""
    Cells(emptyRow, 27).Value = txtPolish.Value
    Cells(emptyRow, 28).Value = ""
    Cells(emptyRow, 30).Value = txtEdge.Value
    Cells(emptyRow, 31).Value = ""
    Cells(emptyRow, 39).Value = txtBullnose.Value
    Cells(emptyRow, 40).Value = ""
    Cells(emptyRow, 42).Value = txtCore.Value
    Cells(emptyRow, 43).Value = ""
    Cells(emptyRow, 45).Value = txtSeal.Value
    Cells(emptyRow, 46).Value = ""
    Cells(emptyRow, 33).Value = txtPackage.Value
    Cells(emptyRow, 34).Value = ""
    Cells(emptyRow, 36).Value = ""
    Cells(emptyRow, 48).Value = txtWjb.Value
    Cells(emptyRow, 49).Value = "Open"
End Sub

Sub AddRow()
    'Add new row to bottom of table
    Range("A" & Rows.Count).End(xlUp).Select
    ActiveCell.EntireRow.Insert
End Sub

This code is what I've cobbled together from various online examples. It may not be the most efficient way of getting the job done but, aside from the issue described above, it works. I think it's the emptyRow portion that is the problem but I'm not sure how to fix it. Any help you can offer is much appreciated!

Also, while trying to figure this out for myself, I've been searching for a good source that explains the VBA syntax. If I understood what each of the commands in each line of code was doing, I could break it down step by step and see where the problem is. Does anyone have a recommendation?

Thanks!
 
Try changing the emptyrow to emptyrow +1 in your cells and see if that makes a difference. If not, then perhaps you should upload a representative sample of your workbook with your code for analyzation.

On second thought after re-reading, suggest you abandon the call and just insert the code from the called Macro into your primary macro.
 
Seems ok to me. You wanted to insert a blank row before the last cell with a value in column A as I understand it.

Debug.Print can be used as a debugging tool. It puts a run's results into VBE's Immediate Window. Click the View menu to display it if needed.

Press F1 with cursor in or next to a word to get specific help.

FWIW: Range("A1")=[A1]=Cells(1,1)=Cells(1,"A"). Range() allows intellisense to work for that object. The other methods have their uses too.

The Find method or the Range() method used in AddRow() can be used. The Range's Offset method is used sometimes but since you are inserting, it should be fine. Note that I removed Select in your routine. It is seldom needed.

Always test on a backup copy.
Code:
Sub Test()
  Dim emptyRow As Long
  emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  Debug.Print "emptyRow", emptyRow
  AddRow
End Sub

Sub AddRow()
  'Add new row to bottom of table
  Dim r As Range
  Set r = Range("A" & Rows.Count).End(xlUp)
  Debug.Print "1row", r.Row
  r.EntireRow.Insert
  Debug.Print "2row, inserted row number", r.Row
End Sub

This code is another way to do it.
Code:
Sub Test()
  Dim i As Long, j As Integer
  'emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  i = rAddRow
  For j = 1 To 4
    Cells(i, j).Value = j
  Next j
End Sub

Function rAddRow(Optional col As String = "A") As Long
  Dim r As Range
  Set r = Cells(Rows.Count, "A").End(xlUp)
  r.EntireRow.Insert
  rAddRow = r.Row - 1
End Function
Tip: If you use the TextBox's Tag property, it would make you code easier to maintain. e.g. Put A as the tag value for column A. If you want to see an example file that does this, please attach your file or I can if needed.
 
Last edited:
This is all you need to write data from the UserForm to a worksheet :

Code:
Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Customers")
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value

    'add more entries following above pattern : ws.Cells(newRow, 3).Value = Me.txtAddress.Value

End Sub


Using the same layout applied to your macro, it would look like this :

Code:
Private Sub cmdAdd_Click()
Dim ws As Worksheet
Dim emptyRow As Long

Set ws = Worksheets("Sheet1")

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

    'Transfer information
    ws.Cells(emptyRow, 1).Value = txtCustomer.Value
    ws.Cells(emptyRow, 2).Value = txtOrder.Value
    ws.Cells(emptyRow, 3).Value = txtDateRecd.Value
    ws.Cells(emptyRow, 4).Value = txtDateReqd.Value
    ws.Cells(emptyRow, 5).Value = ""
    ws.Cells(emptyRow, 6).Value = ""
    ws.Cells(emptyRow, 9).Value = txtBillet.Value
    ws.Cells(emptyRow, 10).Value = ""
    ws.Cells(emptyRow, 12).Value = txtSlab.Value
    ws.Cells(emptyRow, 13).Value = ""
    ws.Cells(emptyRow, 15).Value = txtChip.Value
    ws.Cells(emptyRow, 16).Value = ""
    ws.Cells(emptyRow, 18).Value = txtFlame.Value
    ws.Cells(emptyRow, 19).Value = ""
    ws.Cells(emptyRow, 21).Value = txtSandblast.Value
    ws.Cells(emptyRow, 22).Value = ""
    ws.Cells(emptyRow, 24).Value = txtCut.Value
    ws.Cells(emptyRow, 25).Value = ""
    ws.Cells(emptyRow, 27).Value = txtPolish.Value
    ws.Cells(emptyRow, 28).Value = ""
    ws.Cells(emptyRow, 30).Value = txtEdge.Value
    ws.Cells(emptyRow, 31).Value = ""
    ws.Cells(emptyRow, 39).Value = txtBullnose.Value
    ws.Cells(emptyRow, 40).Value = ""
    ws.Cells(emptyRow, 42).Value = txtCore.Value
    ws.Cells(emptyRow, 43).Value = ""
    ws.Cells(emptyRow, 45).Value = txtSeal.Value
    ws.Cells(emptyRow, 46).Value = ""
    ws.Cells(emptyRow, 33).Value = txtPackage.Value
    ws.Cells(emptyRow, 34).Value = ""
    ws.Cells(emptyRow, 36).Value = ""
    ws.Cells(emptyRow, 48).Value = txtWjb.Value
    ws.Cells(emptyRow, 49).Value = "Open"
End Sub
 
Hi ,

In your AddRow procedure , you are using the following line of code to find out the last row which is non-blank :

Range("A" & Rows.Count).End(xlUp).Select

This is the standard way of finding out the last non-blank row in a worksheet.

But in your main procedure , you are using the following line of code to find out the first blank row in your data :

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

Why not use the same technique to detect the blank row in your data ?

emptyRow = Range("A2").End(xlDown).Row + 1

should give you the correct row. This assumes :

1. That your data or at least your headers are in row 2

2. There is no blank cell anywhere in between in column A

If your headers are in a different row , change the reference A2 to suit.

Narayan
 
Replacing the call with the macro didn't change the outcome but adding +1 to emptyrow worked perfectly. Such a simple solution - thanks for your help!

Try changing the emptyrow to emptyrow +1 in your cells and see if that makes a difference. If not, then perhaps you should upload a representative sample of your workbook with your code for analyzation.

On second thought after re-reading, suggest you abandon the call and just insert the code from the called Macro into your primary macro.
 
I appreciate what you're saying about being consistent, Narayan. I put this code together from a variety of sources and the method used by each source was different. As I learn VBA, I'll be better able to recognize and correct these inconsistencies.

Thanks for your advice!

Hi ,

In your AddRow procedure , you are using the following line of code to find out the last row which is non-blank :

Range("A" & Rows.Count).End(xlUp).Select

This is the standard way of finding out the last non-blank row in a worksheet.

But in your main procedure , you are using the following line of code to find out the first blank row in your data :

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

Why not use the same technique to detect the non-blank row in your data ?


should give you the correct row. This assumes :

1. That your data or at least your headers are in row 2

2. There is no blank cell anywhere in between in column A

If your headers are in a different row , change the reference A2 to suit.

Narayan
 
Back
Top