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

[Ardus Petus]

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

HTH
--
AP

"Jason Paris" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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

--


Gary


"Jason Paris" <[email protected]> wrote in message
news:[email protected]...
> 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
>