Change Static Range of Formula to Dynamic Range
Hello all,
I have this code below that was created by the macro recorder. The only problem I'm having is that I need to run this code again and again and the number of rows will always be different.
How can I change the recorded code below to select the current region of each relevant area and be dynamic instead of static?
Thanks much!ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C18:R50C18,RC[3],R2C14:R50C14,""<>"" &"""")/SUMIF(R2C18:R50C18,RC[3],R2C7:R50C7)"
Last edited by duugg; 01-21-2014 at 05:14 PM.
You may try:
Please Login or Register to view this content.
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
Izandol,
Yes, that worked. Thank you for that!
Is there some "rule-of-thumb" that I can follow to apply that code to similar sections?
Thanks again
Simply replace the last row number in the formula string with some text that will not appear elsewhere - in the example, I used 'lastrow'. You may then use Replace to replace this text with the actual last row number you have calculated.
So, my number of rows will always change, I assuming that the code will work no matter how many rows there are automatically?
What I want to do with your code is to insert it into the middle of a very large module. I want the user to run the module and, without any input, have the code know to stop at the last row of data, whatever row that may be on that given day.
I think you have it that way, but just want to be sure.
Thanks
Yes that is correct.
Re: Change Static Range of Formula to Dynamic Range
Awesome!
Again, thanks much!
Last edited by duugg; 01-24-2014 at 10:18 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks