As part of a Sub, I have the following code that I want to use to fill down
worksheet formulas. Ive defined integer variables for totalRows, lastRow and
lastFilledRow in a defined range, €�StocksFormlas.€� The defined range is
(€œF8:O40€�). Ive also defined a €œy€� variable as Range.
At last run, the returned values for the integer variables are
TotalRows = 33
lastFilledtRow = 14

The first line of code snippet (1), below selects €œF14€�. The second line
does the fillDown correctly, but only if I specify the fillDown arguments in
€œA1€� form. Ive tried everything I know to accomplish the fillDown using
variables to represent the arguments, so that when the address of the last
filledRow changes it can be used as the first argument of the fillDown
method.

Snippet (2) below obviously doesnt work but represents an alternative
approach Ive tried, to no avail. Can someone help me with this? Im growing
old trying to solve this problem.
Thanks very much

(1)
Range("StocksFormulas").End(xlDown).Select
Range("F14:O30").FillDown
(2)
Range("F" & myLastFilledFormulaRow).Select
Set y = Selection
Range("y", Range("O20")).FillDown

I assume that you defined the variable €œlastFilledRow€� as long, then you
include a line of code to give it value (lastFilledRow = €œcode to determine
last filled row€�).

Then you have all you need to make the fill down work as you have described.
The line of code should read:

Range("StocksFormulas").End(xlDown).Select
€˜Dont know why you are selecting this, not needed for fill down, but maybe
you have another use.

Range("F€� & lastFilledRow & €œ:O30").FillDown

That should work although I am not sure of what area you are trying to fill.
Note the way variables can be used in defining a range.

Tom

The problem with your code is enclosing the y in double quotes. Instead of
recognizing it as the variable to which the range is assigned, it becomes the
string y. Just removing the double quotes and it works.

Dim myLastFilledFormulaRow
Dim y As Range

myLastFilledFormulaRow = 10 'For testing only

Range("F" & myLastFilledFormulaRow).Select
Set y = Selection
Range(y, Range("O20")).FillDown

As per your your other reply, there is no need to select the range. The
following is the better code.

Set y = Range("F" & myLastFilledFormulaRow)
Range(y, Range("O20")).FillDown

However, you should realize that the above code only fills down. Your range
goes across to column O. If you want the entire range to be filled then you
need to FillRight also.


Set y = Range("F" & myLastFilledFormulaRow)
Range(y, Range("O20")).FillDown
Range(y, Range("O20")).FillRight

--
Regards,

OssieMac

Thanks,tompl. I'll try that out. I think you and OssieMac have both given me
helpful answers to my question. Your help is greatly appreciated.

jcirish

"tompl" wrote:

I assume that you defined the variable €œlastFilledRow€� as long, then you
include a line of code to give it value (lastFilledRow = €œcode to determine
last filled row€�).

Then you have all you need to make the fill down work as you have described.
The line of code should read:

Range("StocksFormulas").End(xlDown).Select
€˜Dont know why you are selecting this, not needed for fill down, but maybe
you have another use.

Range("F€� & lastFilledRow & €œ:O30").FillDown

That should work although I am not sure of what area you are trying to fill.
Note the way variables can be used in defining a range.

Tom

Thank you, OssieMac. I'll give it a try. As I replied to tompl, I think you
and he have both given me helpful answers to my question. I really appreciate
your help.

jcirish

"OssieMac" wrote:

The problem with your code is enclosing the y in double quotes. Instead of
recognizing it as the variable to which the range is assigned, it becomes the
string y. Just removing the double quotes and it works.

Dim myLastFilledFormulaRow
Dim y As Range

myLastFilledFormulaRow = 10 'For testing only

Range("F" & myLastFilledFormulaRow).Select
Set y = Selection
Range(y, Range("O20")).FillDown

As per your your other reply, there is no need to select the range. The
following is the better code.

Set y = Range("F" & myLastFilledFormulaRow)
Range(y, Range("O20")).FillDown

However, you should realize that the above code only fills down. Your range
goes across to column O. If you want the entire range to be filled then you
need to FillRight also.


Set y = Range("F" & myLastFilledFormulaRow)
Range(y, Range("O20")).FillDown
Range(y, Range("O20")).FillRight

--
Regards,

OssieMac