Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA Code To Prompt User To Insert X Rows

  1. #1
    Join Date
    3rd April 2010
    Posts
    37

    VBA Code To Prompt User To Insert X Rows

    Can any one tell me what is wrong with this code please?

    I am trying to insert rows from an InputBox in several sections of a sheet then copy the formulas down into the inserted cells in each section.

    each row in each section = section one rows

    VB:
    Public rng As Long 
     
    Sub changesheetsize() 
         
        Dim AC_7 As Range 
        Dim AC_6 As Range 
        Dim AC_5 As Range 
        Dim AC_4 As Range 
        Dim AC_3 As Range 
        Dim AC_2 As Range 
        Dim AC_1 As Range 
         
        Set AC_7 = Range("A65536").End(xlUp) 
        Set AC_6 = AC_7.End(xlUp) 
        Set AC_5 = AC_6.End(xlUp) 
        Set AC_4 = AC_5.End(xlUp) 
        Set AC_3 = AC_4.End(xlUp) 
        Set AC_2 = AC_3.End(xlUp) 
        Set AC_1 = AC_2.End(xlUp) 
        Application.ScreenUpdating = False 
         
        rng = InputBox("Enter number of rows required.") 
        AC_1.Select 
        Set AC_1 = AC_1.Offset(-rng, 0) 
        Call InsertRow 
        AC_2.Select 
        Set AC_2 = AC_2.Offset(-rng, 0) 
        Call InsertRow 
        AC_3.Select 
        Set AC_3 = AC_3.Offset(-rng, 0) 
        Call InsertRow 
        AC_4.Select 
        Set AC_4 = AC_4.Offset(-rng, 0) 
        Call InsertRow 
        AC_5.Select 
        Set AC_5 = AC_5.Offset(-rng, 0) 
        Call InsertRow 
        AC_6.Select 
        Set AC_6 = AC_6.Offset(-rng, 0) 
        Call InsertRow 
        AC_7.Select 
        Set AC_7 = AC_7.Offset(-rng, 0) 
        Call InsertRow 
         
        AC_7.Select 
        Call fdownformula 
        AC_6.Select 
        Call fdownformula 
        AC_5.Select 
        Call fdownformula 
        AC_4.Select 
        Call fdownformula 
        AC_3.Select 
        Call fdownformula 
        AC_2.Select 
        Call fdownformula 
        AC_1.Select 
        Call fdownformula 
         
        Application.ScreenUpdating = True 
         
    End Sub 
     
    Sub InsertRow() 
         
        If rng = 0 Then Exit Sub 
        Range(ActiveCell, ActiveCell.Offset(Val(rng) - 1, 0)).EntireRow.Insert 
    End Sub 
     
    Sub fdownformula() 
        Dim n As Long, k As Long 
         'need To know how many formulas To copy down.
         'Assumes from A over To last entry In row.
        k = ActiveCell.Offset(-1, 0).Row 
        n = Cells(k, 256).End(xlToLeft).Column 
        Range(Cells(k, 1), Cells(k + Val(rng), n)).FillDown 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,574

    Re: insert rows, InputBox, filldown

    First reaction, start from AC7, instead of AC1

  3. #3
    Join Date
    3rd April 2010
    Posts
    37

    Re: insert rows, InputBox, filldown

    Now getting run error 1004
    aplication/object defined error
    At

    VB:
    Set AC_1 = AC_1.Offset(-rng, 0) 
    
    
    Last edited by LucyLoo; March 2nd, 2012 at 23:33. Reason: remove picture address

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,574

    Re: insert rows, InputBox, filldown

    Try this..

    VB:
    Sub changesheetsize() 
         
        Dim rng As Range 
        Dim lngRows As Long 
         
        lngRows = InputBox("Enter number of rows required.") 
        For Each rng In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants, 1) 
            rng.Resize(lngRows).EntireRow.Insert 
            rng.Offset(-lngRows - 1).Resize(lngRows + 1, Cells(rng.Row, Columns.Count).End(xlToLeft).Column).FillDown 
        Next rng 
         
    End Sub 
    
    

  5. #5
    Join Date
    8th March 2010
    Posts
    1,408

    Re: VBA Code To Prompt User To Insert X Rows

    VB:
    Sub snb() 
        y = InputBox("Enter number of rows required.") 
        For j = 47 To 5 Step -7 
            Rows(j).Resize(y).Insert 
            Rows(j).Copy Rows(j).Resize(y) 
        Next 
    End Sub 
    
    

  6. #6
    Join Date
    3rd April 2010
    Posts
    37

    Re: insert rows, InputBox, filldown

    Thanks smc that works great. sorry for the delay in coming back; been out of th office.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    3rd April 2010
    Posts
    37

    Re: insert rows, InputBox, filldown

    Ok I have gone away and tried to learn the special cell method is the below explanation of your code correct. If not would you be so kind to put me in the correct direction please.

    VB:
    Sub changesheetsize() 
         
        Dim rng As Range 
        Dim lngRows As Long 
         
        lngRows = InputBox("Enter number of rows required.") 
         
        Set range colum A,                        rng=Cells that contain Numbers 
        For Each rng In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants, 1) 
             
            Resize?  by user Input ( say user Input 10) insert 10 rows 
            rng.Resize(lngRows).EntireRow.Insert 
             
            offset up 9 rows, Resize? (10+1=11rows, need help? 
            rng.Offset(-lngRows - 1).Resize(lngRows + 1, Cells(rng.Row, Columns.Count).End(xlToLeft).Column).FillDown 
             
        Next rng 
    
    
    would this code run smoother if the rng column A references were at the top left of the inserted cells i.e. the row to be filled down from?
    Last edited by LucyLoo; March 8th, 2012 at 00:33. Reason: denote instruction in code

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Macro to Insert User-defined Number of Rows
    By cfd in forum EXCEL HELP
    Replies: 2
    Last Post: June 27th, 2011, 16:25
  2. Replies: 2
    Last Post: November 9th, 2010, 22:49
  3. Prompt user to choose and insert data text file
    By hiubaocong in forum EXCEL HELP
    Replies: 6
    Last Post: October 13th, 2010, 20:23
  4. Count and Insert blank rows based on user input
    By Giga_Me in forum EXCEL HELP
    Replies: 3
    Last Post: August 18th, 2010, 08:16
  5. Insert Rows Within Loop Code
    By raggyryan in forum EXCEL HELP
    Replies: 4
    Last Post: April 3rd, 2008, 11:31

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
  •  
porno