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.
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.
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.
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.
abousettaPlease Login or Register to view this content.
abousetta, use of A65536 is not generally advised these days, instead use:
the above will work irrespective of Version in use.Please Login or Register to view this content.
FWIW I agree with the non-iterative approach.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
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.
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:
orPlease Login or Register to view this content.
Use the macro recorder to find your right formula.Please Login or Register to view this content.
Thank you for you help. I made some corections to the code but I am currently recieving a compile error: "variable not defined",
fPath and fDate have been declared globally.Please Login or Register to view this content.
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
WithPlease Login or Register to view this content.
Please Login or Register to view this content.
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)Originally Posted by AnthonyWB
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!)
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.
Maybe:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks