Announcement

Collapse
No announcement yet.

Insert Formula with VBA

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

  • Insert Formula with VBA

    Hi,

    In the attached sample workbook, I am trying to insert the formula contained in "C2" down column "C" for each row of data. Is this possible using a Loop? Have I entered the code incorrectly?

    All help is greatly appreciated.

  • #2
    Re: Insert Formula with VBA

    Dave

    There's no attachment.
    Boo!

    Comment


    • #3
      Re: Insert Formula with VBA

      Sorry,


      here it is.

      Comment


      • #4
        Re: Insert Formula with VBA

        Norrie:

        I am having difficulty with the attachment. Will try again.

        Comment


        • #5
          Re: Insert Formula with VBA

          Hi,

          I believe the file was too large. Here it is.
          Attached Files

          Comment


          • #6
            Re: Insert Formula with VBA

            Hi Dave... maybe, I am missing something here, but Why dont you just drag the formula down (i.e. "fill down") from C2 to C31.... the formula appears to been have been correctly referenced using $, so the references should remain correct all the way down the rows... ? Or do you specifically want to automate this in VBA? (i.e. you might have 1000's of rows....)

            Ger

            Check out our new reputation system. Click on the "star" under the post!
            _______________________________________________

            There are 10 types of people in the world. Those that understand Binary and those that dont.

            Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

            The BEST Lookup function of all time

            Dynamic Named Ranges are your bestest friend

            _______________________________________________

            Comment


            • #7
              Re: Insert Formula with VBA

              If its VBA you want, you could try this...

              This will fill down the formula from C2... to however many rows there are (or to be more specific, to however many rows has data in Column A).

              Code:
              Public Sub fill_Down()
              
              Dim my_range As Range
              
              With Worksheets("locman")
              
                  Set my_range = .Range("C2:C" & .Range("A65536").End(xlUp).Row)
                  my_range.Formula = _
                      "=IF(ISERROR(VLOOKUP(A2,pickcan!A$2:B$90,2,0))," & """OK to Count""" & ", VLOOKUP(A2,pickcan!A$2:B$90,2,0))"
              End With
              
              End Sub
              HTH
              Ger

              Check out our new reputation system. Click on the "star" under the post!
              _______________________________________________

              There are 10 types of people in the world. Those that understand Binary and those that dont.

              Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

              The BEST Lookup function of all time

              Dynamic Named Ranges are your bestest friend

              _______________________________________________

              Comment


              • #8
                Re: Insert Formula with VBA

                Ger:

                Yes, automating it with VBA is what I was looking for.

                Thanks a million for your help; works like a charm!

                Comment

                Working...
                X