Results 1 to 7 of 7

VBA - Insert new row and edit formula

This is a discussion on VBA - Insert new row and edit formula within the Excel Questions forums, part of the Question Forums category; Hi all, I am trying to copy a row and then paste this on a row that the user has ...

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    6

    Default VBA - Insert new row and edit formula

    Hi all,

    I am trying to copy a row and then paste this on a row that the user has selected. Once this row has been inserted I then want to edit the formulas within each cell - This is where I am having trouble.

    Because the row will be different each time (as the data is pasted into the currently selected row), my code below is useless as this only edits the formula within the cells of a set row.

    Therefore I am trying to work out how once I have pasted the data, I can then edit the formula within the cells of the selected row.

    I hope this makes sense but please let me know if you need this clearing up! My current code is below...


    Code:
        'Unhide rows    Rows("1:6").Select
        Range("A6").Activate
        Selection.EntireRow.Hidden = False
        'Copy selection
        Rows("1:2").Select
        Selection.Copy
        ActiveWindow.SmallScroll Down:=63
        'On selected row, paste
        Rows(newRowNum).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    
    
    Range("D74").Select
        ActiveCell.Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ5"
        Range("E74").Select
        ActiveCell.Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ6"
        Range("F74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-67]C[56]"
        Range("G74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-66]C[55]"
        Range("H74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-65]C[54]"
        Range("I74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-64]C[53]"
        Range("J74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-63]C[52]"
        Range("K74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-62]C[51]"
        Range("L74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-61]C[50]"
        Range("M74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-60]C[49]"
        Range("N74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-59]C[48]"
        Range("O74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-58]C[47]"
        Range("P74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-57]C[46]"
        Range("Q74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-56]C[45]"
        Range("R74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-55]C[44]"
        Range("S74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-54]C[43]"
        Range("T74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-53]C[42]"
        Range("U74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-52]C[41]"
        Range("V74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-51]C[40]"
        Range("W74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-50]C[39]"
        Range("X74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-49]C[38]"
        Range("Y74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-48]C[37]"
        Range("Z74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-47]C[36]"
        Range("AA74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-46]C[35]"
        Range("AB74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-45]C[34]"
        Range("AC74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-44]C[33]"
        Range("AD74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-43]C[32]"
        Range("AE74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-42]C[31]"
        Range("AF74").Select
        ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-41]C[30]"
    
    
    
        'Hide rows
        Rows("1:5").Select
        Range("A5").Activate
        Selection.EntireRow.Hidden = True

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,764

    Default Re: VBA - Insert new row and edit formula

    Welcome to MrExcel.

    If you use FormulaR1C1 and relative references it won't matter where the activecell is.
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Aug 2011
    Location
    AMS
    Posts
    1,709

    Default Re: VBA - Insert new row and edit formula

    change to

    Code:
    Range("D" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ5"
    Range("E" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ6"
    Range("F" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-67]C[56]"
    Range("G" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-66]C[55]"
    Range("H" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-65]C[54]"
    Range("I" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-64]C[53]"
    Range("J" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-63]C[52]"
    Range("K" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-62]C[51]"
    Range("L" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-61]C[50]"
    Range("M" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-60]C[49]"
    Range("N" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-59]C[48]"
    Range("O" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-58]C[47]"
    Range("P" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-57]C[46]"
    Range("Q" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-56]C[45]"
    Range("R" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-55]C[44]"
    Range("S" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-54]C[43]"
    Range("T" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-53]C[42]"
    Range("U" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-52]C[41]"
    Range("V" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-51]C[40]"
    Range("W" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-50]C[39]"
    Range("X" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-49]C[38]"
    Range("Y" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-48]C[37]"
    Range("Z" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-47]C[36]"
    Range("AA" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-46]C[35]"
    Range("AB" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-45]C[34]"
    Range("AC" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-44]C[33]"
    Range("AD" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-43]C[32]"
    Range("AE" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-42]C[31]"
    Range("AF" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-41]C[30]"

  4. #4
    New Member
    Join Date
    Jan 2013
    Posts
    6

    Default Re: VBA - Insert new row and edit formula

    Thanks very much for your help and quick replies, appreciate it! I now have it working

  5. #5
    New Member
    Join Date
    Jan 2013
    Posts
    6

    Default Re: VBA - Insert new row and edit formula

    Hi guys, sorry another question relating to this. Is there a way that I can change the name within the formula to whatever is entered in an input box.

    For example in below, I would like to change the name 'Richard Pitman' to whatever is entered in an input box. Thanks in advance.

    Code:
    Range("E" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]Richard Pitman'!$BJ$6"

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Location
    AMS
    Posts
    1,709

    Default Re: VBA - Insert new row and edit formula

    like this

    Code:
    temp = InputBox("Enter your Data")
    Range("E" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]" & temp & "'!$BJ$6"

  7. #7
    New Member
    Join Date
    Jan 2013
    Posts
    6

    Default Re: VBA - Insert new row and edit formula

    Thanks but I seem to be getting an error.

    Error:
    Run-time error '1004'
    Application-defined or object defined error

    In the debugger I can see that what I enter within the input box is being stored in 'temp' so I'm not sure what the problem is.

    Thanks again.

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
  •  


DMCA.com