Variable Range Length & .FillDown?

[Jason Paris]

Hi all,

I'm putting together some code for a member of our Lending department.
Here's what the code is meant to do:

1. In cell B11, the user will enter the term of a loan in months (eg,
12, 24, 36, etc).

2. A table will then be generated on the same sheet, showing monthly
repayments and other info. The *depth* of that table (ie, its number
of rows) will match the term entered in B11.

For example, if the value in B11 is 12, the table will have a depth of
12 rows.

3. In the left-most column of that table, the EOMonth formula (from
the 'Analysis Toolpack' add-in) will appear. It needs to be filled
down to the bottom of that column.

Here's the issue I'm grappling with: given that the depth of that
column is variable (ie, is dependent upon the value in B11), can I
build a Range("??:??").FillDown statement that accepts a *variable*
address?

For example, if the value in B11 is 12, and the top-left cell of the
resultant table is E23, the bottom-left cell in the table would be E34
(ie, a depth of 12 rows). But if B11 = 13, the address of the table's
bottom-left cell would be E35.

Here's how I've tried to build this flexibility in to the
Range("??:??").FillDown statement......but it throws up a syntax error:

_________________________________________________

Sub PopulateTable()
Dim newTerm

' Get the duration of the Loan Term from cell B11 (eg, 12, 24, 36, etc)
Set newTerm = Worksheets("Sheet1").Range("B11")

' Write the EOMONTH formula in top-left cell of the table
Range("E23").Formula = "=EOMONTH(B10,0)"

' Fill down from E23, giving the table a depth = the value in 'newTerm'
Range("E23").Activate
Range("E23: & ActiveCell.Offset(rowOffset:=(newTerm - 1)) &
").FillDown

End Sub
_________________________________________________

Does that make any sense to you guys?

Any help would be much appreciated.

Cheers,

Jason Paris

 

[Gary Keramidas]

you can try something like this, spelled out so you can follow it

Range("e23:e" & Range("e23").Row + Range("b11").Value).FillDown

or this

Range("e23:e" & 23 + Range("b11").Value).FillDown

 

[Ardus Petus]

Range("E23").Resize(newTerm, 1).Formula = "=EOMONTH(B10,0)"

HTH