Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: VBA - Insert Formula, then copy down column

  1. #1
    Join Date
    4th January 2012
    Posts
    15

    VBA - Insert Formula, then copy down column

    Hi There,

    Searched far and wide and cannot figure out how to:

    Have this formula:

    =IF('Utensils-Portions'!A2="","",'Utensils-Portions'!A2)

    Auto entered via a VBA Macro into cell L5 of the active sheet. Then once entered, copy and paste that formula down the column to cell L106 (all via one VBA Macro).

    So cell L106's value would be:

    =IF('Utensils-Portions'!A103="","",'Utensils-Portions'!A103)

    THANK YOU!
    Last edited by AAE; February 4th, 2012 at 07:11. Reason: remove code tags from formula

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th July 2004
    Posts
    553

    Re: VBA - Insert Formula, then copy down column

    This might work

    VB:
    Sub DoesThisWork() 
        i = 2 
        For Each c In Range("L5:L106") 
            c.Value = "=IF('Utensils-Portions'!A" & i & "="""","""",'Utensils-Portions'!A" & i & ")" 
            i = i + 1 
        Next 
    End Sub 
    
    
    HTH
    John

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    8th July 2004
    Posts
    553

    Re: VBA - Insert Formula, then copy down column

    Sorry, came through twice.

    This might work

    VB:
    Sub DoesThisWork() 
        i = 2 
        For Each c In Range("L5:L106") 
            c.Value = "=IF('Utensils-Portions'!A" & i & "="""","""",'Utensils-Portions'!A" & i & ")" 
            i = i + 1 
        Next 
    End Sub 
    
    
    HTH
    John
    Last edited by jolivanes; February 4th, 2012 at 06:46. Reason: Double posted

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: VBA - Insert Formula, then copy down column

    MikeyG,

    Please do not use code tags with formulas, they are reserved for use when posting VBA code.

    No need for a loop.

    VB:
    Range("L5").Formula = "=IF('Utensils-Portions'!A2="""","""",'Utensils-Portions'!A2)" 
    Range("L5").AutoFill Destination:=Range("L5:L106") 
    
    
    Note the use of the double sets of quotes in the formula - required by VBA.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  5. #5
    Join Date
    8th July 2004
    Posts
    553

    Re: VBA - Insert Formula, then copy down column

    @AAE.
    I tried to adapt the following but with my limited knowledge, I could not do it and that's why I
    ended up with the looping code. Without a loop is better of course.
    The RC notation has me stumped.

    VB:
    Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(, 15) _ 
    .FormulaR1C1 = "=AVERAGE(R1C:R[-1]C)" 
    End Sub 
    
    
    Thanks and Regards
    John

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: VBA - Insert Formula, then copy down column

    @jolivanes,

    Not trying to be persnickety, but this is MickeyG's thread.
    While your question is similar, you should start your own thread.

    Maybe: Copy formula with !R1C1 notation to other cells

    Thanks,
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  7. #7
    Join Date
    8th July 2004
    Posts
    553

    Re: VBA - Insert Formula, then copy down column

    @AAE
    Sorry about that. Was trying to explain what I tried.
    Won't happen again.

    Thanks and Regards
    John

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    4th January 2012
    Posts
    15

    Re: VBA - Insert Formula, then copy down column

    AAE,

    your code worked perfectly! Problem solved, thanks much!

    Sorry about the code tags for formulas. Won't happen again.

    Have a great one!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 5
    Last Post: November 28th, 2011, 15:34
  2. Insert Formula into Changing Last Column
    By eggyu in forum EXCEL HELP
    Replies: 5
    Last Post: June 18th, 2011, 02:53
  3. Replies: 2
    Last Post: December 16th, 2010, 03:37
  4. Find Value In Column & Copy Insert Row
    By bennyw in forum EXCEL HELP
    Replies: 8
    Last Post: February 12th, 2008, 23:47
  5. Insert Column Via Formula/Function
    By naskleng in forum EXCEL HELP
    Replies: 2
    Last Post: September 28th, 2006, 18:24

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