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

[AnthonyWB]

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.

[Dulanic]

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.

[abousetta]

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

[DonkeyOte]

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.

[abousetta]

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

[AnthonyWB]

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.

[Dulanic]

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.

[AnthonyWB]

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.

[DonkeyOte]

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.

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)

[JBeaucaire]

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.

[AnthonyWB]

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.

[JBeaucaire]

Maybe:

Please Login or Register  to view this content.