Announcement

Collapse
No announcement yet.

VBA - Insert Formula, then copy down column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 08:11. Reason: remove code tags from formula

  • #2
    Re: VBA - Insert Formula, then copy down column

    This might work

    Code:
    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

    Comment


    • #3
      Re: VBA - Insert Formula, then copy down column

      Sorry, came through twice.

      This might work

      Code:
      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, 07:46. Reason: Double posted

      Comment


      • #4
        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.

        Code:
        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

        Comment


        • #5
          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.

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

          Comment


          • #6
            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

            Comment


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

              Comment


              • #8
                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!

                Comment

                Working...
                X