+ Reply to Thread
Results 1 to 7 of 7

Add Row and Copy Down Formulae to a Table

  1. #1
    Forum Contributor
    Join Date
    02-24-2008
    Location
    UK
    MS-Off Ver
    2012 and 2016
    Posts
    123

    Add Row and Copy Down Formulae to a Table

    I am writing code to control the input of names by the user to ensure that the names are entered in the correct format so that I can search for names elsewhere in the workbook using the VLOOKUP function.

    The way I have set about this is as follows:

    1. Set up a form to capture the Forename and Surname
    2. Ensure that the form has been completed fully
    3. Select the first cell in the first row below the headings
    4. Insert a blank row below the first row of data
    5. Copy the cells formulae (NOT the name cell) down from the top row to the next row. The row will not be a fixed number of columns. THIS IS THE AREA WHERE I NEED HELP
    6. Insert the name in the blank name cell
    7. Sort the table alphabetically

    This is what I have managed to do so far (below). I have indicated in the code where I think the code should go but I am happy to accept any alternative ideas and code.

    One further development of the code that I am considering is to use reference names for Range variables instead of actual cell addresses (e.g. Range ("B2") becomes Range ("NamedCell") in case the user of the form adds rows about the table.

    Any advice would be much appreciated.

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by kencoburn; 02-09-2019 at 12:43 PM.

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Add Row and Copy Down Formulae to a Table

    I don't know if I understood correctly, but try it (it's without "One further development"):
    (the data for "vlookup" is taken from the table in the 'Sheet2' sheet, the beginning of the table is in 'A1' cell)
    Please Login or Register  to view this content.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Add Row and Copy Down Formulae to a Table

    Hi
    as different versions of Xl can something "clash" with each other, modifying your profile to indicate your XL version ( instead of "old") might help those who wish to help you
    Thank you

  4. #4
    Forum Contributor
    Join Date
    08-21-2018
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    178

    Re: Add Row and Copy Down Formulae to a Table

    HTML Code: 

  5. #5
    Forum Contributor
    Join Date
    02-24-2008
    Location
    UK
    MS-Off Ver
    2012 and 2016
    Posts
    123

    Re: Add Row and Copy Down Formulae to a Table

    First of all, apologies for not responding sooner. I did not get any notifications and so was not aware that the had been responses.

    I have dabbled a bit with VBA but I am by no means anywhere near being an expert. I have had problems trying to get your suggested
    code at #2 to work which is undoubtedly because I am a VBA novice. I want to learn how to write VBA code and so I have devised a simple
    table (see attachment) and used the following code provide above to get started.

    Please Login or Register  to view this content.
    This worked fine. The add AddNameButton would allow me to add a name in cell B17.

    I now want to sort the able on row B (list of names on the real spreadsheet), retaining
    the correct formulae for that person.

    Please advise me how to do this by adding to the above code. I am aware that it would
    probably be easier to write the code with row 7 removed but I want to make the coding bullet proof
    in case the user adds this blank line for styling of the spreadsheet.

    Thanks in anticipation.
    Attached Images Attached Images

  6. #6
    Forum Contributor
    Join Date
    02-24-2008
    Location
    UK
    MS-Off Ver
    2012 and 2016
    Posts
    123

    Re: Add Row and Copy Down Formulae to a Table

    May I seek your advice a little further. The actual table in the spreadsheet that I am working on has a total column at the bottom of the table (e.g. row 18 in the example above). I will therefore need to take this into account as the Rows.Count will not work. The is also another table directly below that table. My thoughts are that the use of the Heading1 cell as a reference cell and the use of code that uses the Range, Offset and End(xlDown) methods would be more appropriate. Just a thought! Can you help on these lines? It may not be the most efficient way of solving the problem but it would be easier for me to understand the logic.

  7. #7
    Forum Contributor
    Join Date
    02-24-2008
    Location
    UK
    MS-Off Ver
    2012 and 2016
    Posts
    123

    Re: Add Row and Copy Down Formulae to a Table

    I have modified the code for my 'real' table that has the structure of the example shown above. It uses offsets from a named cell (RefCell1) that will not change when any additional rows or columns are inserted by the user. However, copying down the formulae from the top row in the table all cells to the right of the top names will only work if no additional rows or columns are inserted by the user. I would be grateful for any help to amend the section shown between ============= lines to allow for the addition of rows and columns.


    Please Login or Register  to view this content.
    Last edited by kencoburn; 02-18-2019 at 06:34 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. [SOLVED] formulae not able to automatically copy down in cells in a table
    By anilpatni1234 in forum Excel General
    Replies: 7
    Last Post: 12-22-2018, 11:01 AM
  2. Replies: 1
    Last Post: 10-01-2018, 08:46 AM
  3. Formulae in a table
    By dizzymop in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 04-05-2018, 11:50 AM
  4. [SOLVED] Copy formulae to range without Copy/Paste
    By astrikor in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-19-2016, 05:31 AM
  5. Copy a table to multiple sheets and advance the formulae
    By benjaminjrp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2013, 06:15 PM
  6. Table Formulae
    By Ron12 in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 04-27-2013, 09:57 AM
  7. [SOLVED] copy between worksheets does not copy formulae just values
    By Chris@1000 Oaks in forum Excel General
    Replies: 0
    Last Post: 03-19-2006, 07:50 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