dynamic range defined in VBA for use in formulas

Hi, I need some help with dynamic ranges and filling in formulas in the
spreadsheet. Basically, I copied and pasted data into column A, which drives
formulas in column B. The formulas in column B extend longer than the data
in column A, so i end up with a bunch of zeros at the end. I used the
following VBA code to define the non-zero range in column B.

Dim LastRow As Long
Dim rng As Range
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("B1:B" & LastRow)

In column C of my spreadsheet are various percentiles that i need to find.
here's my question: I want to fill in a formula in column D that uses the
range that i defined in the VBA code and the percentiles stipulated in column
C. How do I code that in VBA? Thanks for any help that you can provide!

I'd suggest that instead of using VBA, you use dynamic ranges defined as
named ranges. Take a look at

http://cpearson.com/excel/named.htm#Dynamic

In article <[email protected]>,
"yo" <[email protected]> wrote:

> Hi, I need some help with dynamic ranges and filling in formulas in the
> spreadsheet. Basically, I copied and pasted data into column A, which drives
> formulas in column B. The formulas in column B extend longer than the data
> in column A, so i end up with a bunch of zeros at the end. I used the
> following VBA code to define the non-zero range in column B.
>
> Dim LastRow As Long
> Dim rng As Range
> LastRow = Range("A65536").End(xlUp).Row
> Set rng = Range("B1:B" & LastRow)
>
> In column C of my spreadsheet are various percentiles that i need to find.
> here's my question: I want to fill in a formula in column D that uses the
> range that i defined in the VBA code and the percentiles stipulated in column
> C. How do I code that in VBA? Thanks for any help that you can provide!

Check column B

LastRow = Range("B" & Rows.Count).End(xlUp).Row


--

HTH

RP
(remove nothere from the email address if mailing direct)


"yo" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need some help with dynamic ranges and filling in formulas in the
> spreadsheet. Basically, I copied and pasted data into column A, which
drives
> formulas in column B. The formulas in column B extend longer than the
data
> in column A, so i end up with a bunch of zeros at the end. I used the
> following VBA code to define the non-zero range in column B.
>
> Dim LastRow As Long
> Dim rng As Range
> LastRow = Range("A65536").End(xlUp).Row
> Set rng = Range("B1:B" & LastRow)
>
> In column C of my spreadsheet are various percentiles that i need to find.
> here's my question: I want to fill in a formula in column D that uses the
> range that i defined in the VBA code and the percentiles stipulated in
column
> C. How do I code that in VBA? Thanks for any help that you can provide!