Trying to copy a forumla down a column using a variable for the end.

Posted by Brian A. on April 19, 2001 1:48 PM

Hi,

I'm trying to write a fomula in a cell from a macro then
copy the formula down to the cells under it row X. Row #
X is held in a variable markhold.

Here's my code:

.Range("Q18").Select
ActiveCell.FormulaR1C1 = "= RC[-1]/RC[-5]"
Selection.AutoFill Destination:=Range("Q18" & ":" & "Q" & markhold), Type:=xlFillDefault

I want the formula to copy from tow 18 to the variable row but the code bombs on the selection autofill line.

Why?

Thanks in advance for your help.

Brian

Posted by Dave Hawley on April 19, 2001 7:44 PM

Hi Brian

It very rare you need to use AutoFill in VBA as you can just do this:

Range("Q18:Q" & markhold) = "= RC[-1]/RC[-5]"

But Having said this your code should work! As you say it doesn't I would say either your varaible "markhold" is not set to a number greater than 18 or it is not a number at all.

Dave

OzGrid Business Applications

Posted by Brian A. on April 20, 2001 9:02 AM

Your code worked, thanks Dave!

Thanks Dave. I replaced my code with yours and it worked!
I checked in debug mode and mt varaiable had a value of
479 in it so I'm not sure why it didn't work but in any event, thanks again!

Brian