+ Reply to Thread
Results 1 to 9 of 9

Copying & adding rows as per specified values

  1. #1
    Registered User
    Join Date
    11-25-2020
    Location
    Doncaster, England
    MS-Off Ver
    2010
    Posts
    4

    Copying & adding rows as per specified values

    Hi all,

    I'm completely green at this moment in terms of VBA, but I know exactly what I need. Hopefully, this is a painless thing for someone to help with

    I basically need a script which:
    - filters column C by #N/A (result of a formula) - OR - does steps below before applying a filter, based on a match in C column (has to be #N/A)
    - looks at each row of visible data, column B
    - duplicates each row times equal to values in column B, if value is more than 1 then duplicates the whole row by that amount of times
    Column A is just a reference number in this case.

    Sample spreadsheet attached.

    Thanks in advance!
    Mat
    Attached Files Attached Files

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Copying & adding rows as per specified values

    Please Login or Register  to view this content.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Copying & adding rows as per specified values

    Mat,

    Starts with unfiltered sheet, filters for #N/A, copies a record as many times as necessary if the Value is more than one, resorts into Ref order, and re-filters at the end.

    Please Login or Register  to view this content.
    Ochimus
    Last edited by Ochimus; 11-26-2020 at 07:39 AM.

  4. #4
    Registered User
    Join Date
    11-25-2020
    Location
    Doncaster, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Copying & adding rows as per specified values

    Hi both,

    Thanks for your replies. Unfortunately, I get runtime error 1004 for both scripts, when running on real data.

    Ochimus I get runtime error 438 when running your script on sample spreadsheet.

    JLGWhiz The result comes out different than the sheet after. Rows number is incorrect when running your script on sample spreadsheet.

    What could be the cause?

    THanks
    Mat

  5. #5
    Registered User
    Join Date
    11-25-2020
    Location
    Doncaster, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Copying & adding rows as per specified values

    Hi guys,

    Just tried both on my work PC with Office 365 installed.

    Ochimus - your script seems to be working perfectly, thank you!

    JLGWhiz - your script seems to be almost working well, the only issue it's got is it simply adds rows as many as per 'value' column. So if the value is 1, it adds 1 more row, resulting in 2 rows with the value of 1. The row number should equal to the 'value' - so if value = 1, row number = 1 as so on..

    Thank you both I will be using your script Ochimus #Star!

    Cheers
    Mat

  6. #6
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Copying & adding rows as per specified values

    Mat,

    No idea why you get the error. There are no "compatability" issues when I run it as an earlier version on my desktop.

    Attached is workbook in 2003 version, so hopefully works perfectly your end?

    Ochimus
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-25-2020
    Location
    Doncaster, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Copying & adding rows as per specified values

    Hi Ochimus,

    Thanks for your input in this.
    I downloaded & ran the file you've attached and the script seems to be stopping halfway and this time, a pop-up window appears saying runtime error '438' (this time lets me 'End' or 'Debug' etc.).

    Weird. I have different language set for my Windows 10 from the one in Office 2010, could that be an issue? I run Office in English tho.

    P.S. That being tested on my personal PC, didn't try this yet on my work PC.

    Cheers
    Mat

  8. #8
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Copying & adding rows as per specified values

    Mat,

    Sorry the problem is still there.

    Can you "debug" and tell me where the Code is stopping?

    Ochimus

  9. #9
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Copying & adding rows as per specified values

    Quote Originally Posted by zuczkins123 View Post
    Hi guys,

    Just tried both on my work PC with Office 365 installed.

    JLGWhiz - your script seems to be almost working well, the only issue it's got is it simply adds rows as many as per 'value' column. So if the value is 1, it adds 1 more row, resulting in 2 rows with the value of 1. The row number should equal to the 'value' - so if value = 1, row number = 1 as so on..

    Cheers
    Mat
    Just for continuity, here is modified version:

    Please Login or Register  to view this content.

+ 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. Copying Formula When Adding Rows With Macro
    By angusfire in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-12-2020, 04:01 AM
  2. [SOLVED] Locking cells in columns but allow copying and adding rows
    By Skully in forum Excel General
    Replies: 16
    Last Post: 02-25-2020, 10:49 AM
  3. [SOLVED] VBA - Copying columns, adding rows
    By MoldyBread in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2014, 11:19 PM
  4. [SOLVED] Copying Rows and Adding to an Integer
    By abba92 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2013, 04:12 PM
  5. Copying colums of formula and adding blank rows
    By tallgingerbloke in forum Excel General
    Replies: 8
    Last Post: 07-02-2011, 01:59 PM
  6. adding and copying rows
    By CatherineN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2006, 05:07 PM
  7. Copying and skipping (adding) rows
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-22-2006, 02:40 PM

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