+ Reply to Thread
Results 1 to 3 of 3

insert and adjust formula series below newly entered row in table

Hybrid View

  1. #1
    Registered User
    Join Date
    01-15-2018
    Location
    medford, oregon
    MS-Off Ver
    2010
    Posts
    21

    insert and adjust formula series below newly entered row in table

    I am trying to figure out how to keep the formulas in series in rows in the table, after inserting new row.

    right now when inserting "new user" that row keeps formatting, but does not insert a formula.

    example..

    =IF(C9="","",C9+1095)
    =IF(C10="","",C10+1095)
    =IF(C11="","",C11+1095)

    How could I acomplish this by inserting code into current code?
    any help is appreciated.

    Option Explicit
    Dim LObj As ListObject
    
    Private Sub Removeusernow_Click()
    Dim i&
    i = ComboBox1.ListIndex
    If i = -1 Then MsgBox "Non-existent name: remove cancelled.": Exit Sub
    ComboBox1.RowSource = ""
    LObj.ListRows(1 + i).Delete
    ComboBox1.RowSource = LObj.Name
    Unload Me
    End Sub
    
    Private Sub UserForm_Initialize()
    Set LObj = Range("Tabla1").ListObject
    ComboBox1.RowSource = LObj.Name
    End Sub
    
    Private Sub Addusernow_Click()
    Dim i&
    i = ComboBox1.ListIndex
    If i > -1 Then MsgBox "Existing name: addition cancelled.": Exit Sub
    ComboBox1.RowSource = ""
    If ComboBox1 < LObj.Range(2, 1) Then
      LObj.ListRows.Add(1).Range(1) = ComboBox1
    Else
      i = WorksheetFunction.Match(ComboBox1, LObj.ListColumns(1).DataBodyRange)
      If i < LObj.ListRows.Count Then
        LObj.ListRows.Add(1 + i).Range(1) = ComboBox1
      Else
        LObj.ListRows.Add.Range(1) = ComboBox1
      End If
    End If
    ComboBox1.RowSource = LObj.Name
    Unload Me
    End Sub
    Certification.xlsm
    Last edited by new guy; 09-01-2022 at 11:59 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: insert and adjust formula series below newly entered row in table

    Hello.
    What I did was convert the ListObject to a cell range and then convert it back to a ListObject.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-15-2018
    Location
    medford, oregon
    MS-Off Ver
    2010
    Posts
    21

    Re: insert and adjust formula series below newly entered row in table

    sorry for the late responce, I have been trying to break down and understand list object code, i can not figure out why columbs P and R, the formulas do not get entered.

    any suggestions?
    Attached Files Attached Files
    Last edited by new guy; 09-04-2022 at 02:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. adjust table formulas after insert
    By new guy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-31-2022, 12:25 PM
  2. Newly entered data disappears
    By Harmony60 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 01-23-2015, 12:08 PM
  3. [SOLVED] Insert row in table after entered data
    By bidsinga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2014, 05:35 PM
  4. [SOLVED] How to adjust this formula to keep "Not yet entered" cells blank
    By Shrad013 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2013, 12:02 PM
  5. vba to insert new row AND highlight NEWLY INSERTED ROW
    By wanty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2013, 11:56 PM
  6. Excel Formula to adjust to data entered
    By hb062609 in forum Excel General
    Replies: 1
    Last Post: 05-03-2012, 07:57 PM
  7. how to adjust chart series formula to include the "indirect" function
    By luv2glyd in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 06-13-2010, 07:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1