How to use a variable as an argument in .FliiDown

[JCIrish]

As part of a Sub, I have the following code that I want to use to fill down
worksheet formulas. I’ve defined integer variables for totalRows, lastRow and
lastFilledRow in a defined range, â€StocksFormlas.†The defined range is
(“F8:O40â€). I’ve 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. I’ve 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 doesn’t work but represents an alternative
approach I’ve tried, to no avail. Can someone help me with this? I’m 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

 

[tompl]

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
‘Don’t 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

 

[OssieMac]

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

 

[JCIrish]

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

 

[JCIrish]

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