I want to copy down formula present in a row till it meets a certain condition. (VBA)

Problem.jpg

Here in the attached image you can find A3:D3 has the formulas. Each time the row is filled down the wallet size will get reduced. I want to write a macro to perform this operation to fill down cells till the wallet size goes below 100. Thanks in advance

You should post a sample workbook, instead just a screenshot.

Find it why, in the forum rules page :
http://www.excelforum.com/forum-rule...rum-rules.html

and search for keyword :
Want to get your question answered quickly?
Post a WORKBOOK


The code you expected is something like this :

Please Login or Register  to view this content.

THanks for the quick help karedog and kaper.

@karedog

I have copied the script to the sheet but it isnt working as i wanted. And can you breakdown the script. i am a newbie here. Thanks.

And @kaper there is some problem in attaching worksheets, idk :/

Try the attached file, is this not what you expected ?

WOw!! this is exactly what i asked for.

Brilliant work.

Actually, It takes time to calculate the result i wasnt waiting. Thank u so much

You are welcome, and thanks for the reputation point given.


Regards

Hey karedog,

so one more question, here basically in the file you have attached i see it fills down till the end of the sheet and then it removes the cells which are above 1000. How can make this filling stop once the match 1000 is found instead of filling the whole worksheet. because I am planning to use this technique to a different sheet which uses bit complex formulas which actually takes a really long time to calculate. THanks in advance.

I believe that will make the calculation slower, instead faster. If we do put formulas cell by cell then examine whether the condition is already met, then there are excessive overhead call between vba (the macro) and the range object, if you want to get the result as fast as possible, you should use Excel's solver feature instead, but solver cannot display the calculation result step by step, as the formulas do.

Adding a comments to karedog's last post.

Running solver with a macro has not been possible in some Mac version of Excel because

There is a serious problem using Solver for Excel in Office 2011 when it is called from vba. Because Solver is a separate application, control must be transferred from Excel to Solver, and this control does not occur until after vba has terminated. This means that any processing that occurs in the vba program after the call to SolverSolve will be completed before Solver has done its task.

The OP uses Mac Office 16 so perhaps the problem has been fixed now but this should be tested first i.e. running solver in a macro.

Alf

Thanks a lot guys. that helps

@Alf: Thanks for the nice info.

@balajidream: You are welcome, glad to help.


Regards