Entering an array formula into cell , Modify it to suit need

**cbhawsar** · 05-28-2011, 02:45 PM

Hi,
In my excel code, i need to enter an array formula into cell and autofill the formula upto the last row.
the formula is as below

Please Login or Register  to view this content.

How can I modify the formula , so that the workbook name "RNDCIQ Check V70.xlsm" is replaced by a variable RNDtool (RNDtool holds the value of ActiveWorkbook.name)?

Also, I need to modify "Input'!R2C5:R68C5". It should be R2C5 to last non empty row.

**TMS** · 05-28-2011, 05:59 PM

I've put the formulae in different cells so you can see how it builds up:

Please Login or Register  to view this content.

Regards

**cbhawsar** · 05-29-2011, 01:33 AM

Oh Great.. Thanks.

I used this formula.

Please Login or Register  to view this content.

I have another question. In the last line of the above code, excel autofills the formula upto the last row. but it takes a lot of time for it to happen. Is this usual? please suggest if there is some way I can optimize it.

**TMS** · 05-29-2011, 03:00 AM

You need to step through the code and see what it is doing, what the variables hold, which cells are autofilled, etc.

Regards

**cbhawsar** · 05-29-2011, 03:28 AM

I am sorry may be I could not explain the problem well.
I used the following code to fill in the array formulas quickly

Please Login or Register  to view this content.

but when vba enters the formula into the cells, The R1C1 style is not maintained. It gets converted into A1 style reference.
How do I modify this to make sure RC[-105]:RC[-2] is maintained according to each row.

currently all rouws have the same formula.
I need
for row2 A2:CZ2
for row3 A3:CZ3 etc

**TMS** · 05-29-2011, 10:06 AM

Sorry, you're moving the goalposts.

I answered the original question ... both parts.

This is a variation, maybe on a theme, but I'd suggest you close this threas as solved and start a new one.

Regards

**cbhawsar** · 05-29-2011, 10:39 AM

I was not moving the goalpost, I was trying to get the shots in the right direction.
But Thanks for your answer, I am atleast somewhere now.

I found the answer with some research and it is straight.
I was using an array formula with 68 cells on one end and 103 cells on the other end. so each time the autofill entered the formula, it had to calculate for 68*103 times a block of code.

and since the formula was supposed to be filled in to >2000 cells,
It took a hell lot of time.
So, as a solution i reduced the number of columns it had to process.
And made it RC[-105]:RC[-75]

Please Login or Register  to view this content.

**TMS** · 05-29-2011, 11:10 AM

You know, it is common courtesy and forum rules to advise everyone if you are cross posting:

http://www.ozgrid.com/forum/showthre...507#post557507

This rule is to save people wasting their time working on a problem that may be resolved elsewhere.

I'm glad you have addressed your problem.

Regards

**cbhawsar** · 05-29-2011, 12:47 PM

You are correct. I will take care of it from now on.
Thanks for the advice

Entering an array formula into cell , Modify it to suit need

LinkBack

Thread Tools

Rate This Thread

Display

Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Re: Entering an array formula into cell , Modify it to suit need

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions