Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 34

Thread: VBA to insert rows
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,352
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    Yes, using the data from your post #18 , I get the same results. Now change your formula in cell B4 to =R6*S6, B5 =R7*S7 and tell me what you get...
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  2. #22
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert rows

    DATE PRESC NO CUSTOMER SITE
    #REF! 0 43678 123456789
    00/01/1900 43678 123456789 CustA
    01/08/2019 123456789 CustA SiteA

    here is whats happening MC

  3. #23
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,352
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    Quote Originally Posted by mc136355 View Post
    DATE PRESC NO CUSTOMER SITE
    #REF! 0 43678 123456789
    00/01/1900 43678 123456789 CustA
    01/08/2019 123456789 CustA SiteA

    here is whats happening MC
    Both my code and Akuini's codes returns the date correctly. Is the date the result of a formula or is it static.
    Last edited by igold; Aug 24th, 2019 at 12:48 PM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  4. #24
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert rows

    Hi

    i apologise the date is a formula but i realise it needs to be absolute ref. i have =C2 should be =$C$2 OOps MC

  5. #25
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,352
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    Are any of the codes working for you...


    @Akuini - the fact that the OP was getting the date error without an absolute reference, reinforces what I was experiencing with the formula autofilling with changed references
    Last edited by igold; Aug 24th, 2019 at 01:09 PM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  6. #26
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert rows

    Before code
    COUNT
    =COUNTIF(F$15:F15,F15)

    After code
    COUNT
    =COUNTIF(F15:F$16,F15)
    =COUNTIF(F$16:F16,F16)
    =COUNTIF(F$15:F17,F17)

    This is the only problem i find. the countif just numbers the rows 1 to whatever. This is getting mixed up. MC

  7. #27
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    Quote Originally Posted by igold View Post
    Yes, using the data from your post #18 , I get the same results. Now change your formula in cell B4 to =R6*S6, B5 =R7*S7 and tell me what you get...

    If column R:S is part of the table then formula in B4 stays the same, but if it isn't then the formula changes to =R4*S4

  8. #28
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert rows

    Akuini the code that you have provided seams to resolve this issue. At the moment i cant seam to find any further issue. Thanks MC

  9. #29
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,352
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    That is the answer then, as my formulas were referencing cells outside of the table.

    However for my own benefit I am trying to write a solution that will have the desired result with references to non-table cells.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  10. #30
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    Quote Originally Posted by mc136355 View Post
    Akuini the code that you have provided seams to resolve this issue. At the moment i cant seam to find any further issue. Thanks MC
    Are you sure?
    Actually I find my code produce the issue as you described at post #26
    Please try this one:

    Code:
    Sub a1107749c()
    'https://www.mrexcel.com/forum/excel-questions/1107749-vba-insert-rows.html
    
        Dim x As Long
        
        Application.ScreenUpdating = False
        x = Range("A1").Value
    
        With ActiveSheet.ListObjects("medline")
            .DataBodyRange.Rows("2:" & x + 1).Insert
            .DataBodyRange.Rows(1).Copy .DataBodyRange.Rows("2:" & x + 1)
        End With
        
        Application.ScreenUpdating = True
        
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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