+ Reply to Thread
Results 1 to 12 of 12

A for loop which will place formulas into cells going down a column

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    A for loop which will place formulas into cells going down a column

    Hello everyone. I have a formula which I have written in VBA Code. I would like to know how to write a for loop which will place the formula into the cells going down a column.
    Last edited by AnthonyWB; 05-20-2010 at 09:20 AM.

  2. #2
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: A for loop which will place formulas into cells going down a column

    Adjust range as needed or use in Column instead of in Range. Change formula after FormulaR1C1 =. If unsure how to write the formula in VBA use macro recorder and enter the formula once to see what it tells you it should be.

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: A for loop which will place formulas into cells going down a column

    Hi,

    Here's another approach I like to use that will loop based on the contents on another column. In this case the reference column is A.

    Please Login or Register  to view this content.
    abousetta

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: A for loop which will place formulas into cells going down a column

    abousetta, use of A65536 is not generally advised these days, instead use:

    Please Login or Register  to view this content.
    the above will work irrespective of Version in use.

    FWIW I agree with the non-iterative approach.

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: A for loop which will place formulas into cells going down a column

    Thanks DonkeyOte. I didn't know this. In fact only a few weeks ago I knew nothing about vba. So thanks for pointing this out. I will change my practices to take account of this.

    abousetta

  6. #6
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: A for loop which will place formulas into cells going down a column

    I am going to use the for loop it seems more straightforward than the offset. I am getting an error however. Please see the code below, thanks. I believe C may have been declared incorrectly?

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: A for loop which will place formulas into cells going down a column

    Check the formula, it is not correct. Depending if you need variable or not....

    FYI c does not need to be declared.

    http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

    These are if I am pasting into A:

    Please Login or Register  to view this content.
    or


    Please Login or Register  to view this content.
    Use the macro recorder to find your right formula.

  8. #8
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: A for loop which will place formulas into cells going down a column

    Thank you for you help. I made some corections to the code but I am currently recieving a compile error: "variable not defined",

    Please Login or Register  to view this content.
    fPath and fDate have been declared globally.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: A for loop which will place formulas into cells going down a column

    The prior suggestion from abousetta was more concerned with avoiding Iteration as it can be a relatively slow method, whenever possible work with ranges en masse.

    Replace

    Please Login or Register  to view this content.
    With

    Please Login or Register  to view this content.
    Quote Originally Posted by AnthonyWB
    I made some corections to the code but I am currently recieving a compile error: "variable not defined"....
    If you're running Option Explicit (advised) then each variable must be declared - in your prior code c would be declared as a Range (not as an Integer per your earlier code)

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: A for loop which will place formulas into cells going down a column

    Not addressing your variable issues, if you're going to simply insert the formula into a set range, then why not just do it directly without looping, it's the same result?

    Based on your wanting to put this formula (from post #6) in Z2 downward:
    =($V2-Right($K$1,11))/365

    Please Login or Register  to view this content.


    EDIT: Hat-tip to Don.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  11. #11
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: A for loop which will place formulas into cells going down a column

    I will try to avoid the looping as it far more taxing on my performance than I thought it would be. The total block of code is below.

    Please Login or Register  to view this content.

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: A for loop which will place formulas into cells going down a column

    Maybe:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1