Create New row in a table and copy/update formulas VBA

**whitenoise22** · 07-22-2012, 04:55 PM

Hi, I'm new to VBA

I've got my excel table that uses certain formulas to calculate values for some cells.
I use UserForm to fill in that table however when new row with input from the form gets added to the table it does not inherit updated formulas from the row above. Therefore no calculations are taking place in that row. I use the following code to insert a new row with UserForm data:

Private Sub submit_Click()

Dim nextrow As Integer

nextrow = WorksheetFunction.CountA(Sheets("Data").Range("B:B")) + 1

Sheets("Data").Cells(nextrow, 2) = DTPicker1.Value
Sheets("Data").Cells(nextrow, 3) = odo.Value
Sheets("Data").Cells(nextrow, 8) = fuel.Value
Sheets("Data").Cells(nextrow, 9) = price.Value
Sheets("Data").Cells(nextrow, 14) = ComboBox1.Value
Sheets("Data").Cells(nextrow, 15) = notes1.Value

Unload fillupEntry

End Sub

Any help would be greatly apreciated. Thanks

**whitenoise22** · 07-23-2012, 03:31 AM

Bump

**patel45** · 07-23-2012, 05:11 AM

Attach please a sample of your file for checking

**whitenoise22** · 07-23-2012, 06:10 AM

I'd like to but I've got confidential data in there. Sorry.

**whitenoise22** · 07-23-2012, 06:18 AM

OK I stripped down the file from all other sheets and confidential data

**patel45** · 07-23-2012, 09:09 AM

I tested your file, for me it works, calculations are taking.
Maybe some macro disables the calculations

**whitenoise22** · 07-23-2012, 10:27 AM

Thank you for your help so far. That's true it is working in that file indeed. I've missed that because I was working with later version. I think problem might have something to do with other functions that I've added later. Please see the attached file.

Many Thanks!

**patel45** · 07-23-2012, 02:16 PM

In the sheet Data I don't see formulas in the blank cells, why ?

**whitenoise22** · 07-23-2012, 05:30 PM

I get it. Thank you. I thought it will automatically copy the formula when creating a new row so I don't have to copy it down manually. Is there a way to achieve this without stretching down formula copy frame in each column ?

Also the other problem is that I've got garages listed in Garages sheet and this is fed to ComboBox1 where two columns from 'Garages' are displayed A&B; However when the selection is made in fillupEntry form only the value from column 'B' is passed to the cell in 'Data' column 'N'.
Any chance you could also help me on this? I need that selection from the comboBox is passed to 'N' column in 'Data' as for instance 'A2 & B2' (Tesco Bangor)

**patel45** · 07-23-2012, 11:41 PM

Originally Posted by whitenoise22

I get it. Thank you. I thought it will automatically copy the formula when creating a new row so I don't have to copy it down manually.

I don't understand how, explain it me. In the test sheet the formula were present in the blank cells and these were accessible.
I'm not a guru

**royUK** · 07-24-2012, 02:31 AM

Why does your profile show Excel 2003, but you add a 2007/2010 workbook?

Private Sub submit_Click()
    Dim nextrow As Long
    
    With Sheets("Data").Range("B6").ListObject
        .ListRows.Add AlwaysInsert:=False
        nextrow = .Range.Rows.Count + 1
        .Range.Cells(nextrow, 1) = DTPicker1.Value
        .Range.Cells(nextrow, 2) = odo.Value
        'etc
    End With
    Unload fillupEntry
End Sub

**whitenoise22** · 07-24-2012, 05:06 AM

Originally Posted by royUK

Why does your profile show Excel 2003, but you add a 2007/2010 workbook?

Because it was hanging the page when I tried. Now it is OK

Thank you for your help Roy. I've used the code but it seems to add two rows at the time. One empty (row '11') and one correct below. Therefore formulas are not working as they are related to the data in the row above.

Also in C11 suddenly I get this formula

=IF(E11>0, C10+E11, 0)

and I don't know where it is from. At the same time other cells in row '11' have no formula copied over.

Could you please point me to where I get it wrong? Thanks

Tom

**royUK** · 07-24-2012, 08:18 AM

This seems to be working

Private Sub submit_Click()
    Dim nextrow As Long
    With Sheets("Data").Range("B6").ListObject
        .ListRows.Add AlwaysInsert:=False
        nextrow = .Range.Rows.Count
        .Range.Cells(nextrow, 1) = DTPicker1.Value
        .Range.Cells(nextrow, 2) = odo.Value
        'etc
    End With
    Unload fillupEntry
End Sub

**whitenoise22** · 07-24-2012, 09:07 AM

Thanks. I will check this

---------- Post added at 08:07 AM ---------- Previous post was at 07:41 AM ----------

Thanks! Now it's not adding an extra row.

But still It is not copying down formulas from (only) col D and G in 'Data' other formulas however are copied down to the newly created row.

This is the code I currently use:

Private Sub submit_Click()
    Dim nextrow As Long
    With Sheets("Data").Range("B6").ListObject
        .ListRows.Add AlwaysInsert:=False
        nextrow = .Range.Rows.Count
        .Range.Cells(nextrow, 1) = DTPicker1.Value
        .Range.Cells(nextrow, 2) = odo.Value
        .Range.Cells(nextrow, 7) = fuel.Value
        .Range.Cells(nextrow, 8) = price.Value
        .Range.Cells(nextrow, 13) = ComboBox1.Value
        .Range.Cells(nextrow, 14) = notes1.Value
    End With
    Unload fillupEntry
End Sub

**royUK** · 07-25-2012, 03:07 AM

Checking the table manually not all the formulas copy. I suggest you try reformatting it as a Table, it doesn't seem to be picking up some formulas

Create New row in a table and copy/update formulas VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Re: Create New row in a table and copy/update formulas VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions