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!

dynamic range defined in VBA for use in formulas
 

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 ,
"yo" 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!

dynamic range defined in VBA for use in formulas
 

Check column B

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


--

HTH

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


"yo" wrote in message
...
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!