dynamic range defined in VBA for use in formulas

[Guest]

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!

 

[JE McGimpsey]

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

 

[Bob Phillips]

Check column B

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


--

HTH

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