Changing Range in VBA to Select First Row of Table

Question

Currently, in Excel VBA, I have a simple function that reads in a set of values from the first row of a worksheet, which it later uses in an equation.

equity(i) = Worksheets("Data").Range("D2").Offset(i - 1, 0)
debt(i) = Worksheets("Data").Range("E2").Offset(i - 1, 0)
riskFree(i) = Worksheets("Data").Range("F2").Offset(i - 1, 0)

I want to change this so that the function will read in the values only from a highlighted selection on the worksheet. So if I select a table of values from the data starting at the 20th row, it should read in D20, E20 F20 etc instead of D2, E2 and F2 as it is set up now. I have tried:

equity(i) = Worksheets("Data").Range("D" & Rows.Count).end(xlUp).Offset(i - 1, 0)
debt(i) = Worksheets("Data").Range("E" & Rows.Count).end(xlUp).Offset(i - 1, 0)
riskFree(i) = Worksheets("Data").Range("F" & Rows.Count).end(xlUp).Offset(i - 1, 0)

but with no luck. What am I doing wrong?

What I have so far looks like this

Function VarunModel(Table As Range, Optional EndCondition As Integer = 0) As Variant


Dim iNumCols As Integer, iNumRows As Integer
Dim i As Integer


iNumCols = Table.Columns.Count
iNumRows = Table.Rows.Count

maturity = Worksheets("KMV-Merton").Range("B2").Value
For i = 1 To iNumRows
equity(i) = Worksheets("Data").Range("D2").Offset(i - 1, 0)
debt(i) = Worksheets("Data").Range("E2").Offset(i - 1, 0)
riskFree(i) = Worksheets("Data").Range("F2").Offset(i - 1, 0)
Next I

user4039065 · Accepted Answer · 2015-06-11 20:15:53Z

0

If you want the value from D20 when you are passed a range of D20:F50, simply treat the D20:F50 range as its own subset of cell; like a miniature independent worksheet.

with Worksheets("Data")
    equity(i) = .Range("D20:F50").Range("A1").Value    '◄ D20
    debt(i) = .Range("D20:F50").Cells(1, 2).Value      '◄ E20
    riskFree(i) = .Range("D20:F50").Range("C3").Value  '◄ F22
end with

When you are dealing with a range of cells that you have isolated off, all range and cell references are relative to the range of cells; e.g. A1 is the cell in the top-left corner. Another way of writing the above would be,

with Worksheets("Data").Range("D20:F50")
    equity(i) = .Range("A1").Value    '◄ D20
    debt(i) = .Cells(1, 2).Value      '◄ E20
    riskFree(i) = .Range("C3").Value  '◄ F22
end with

answered Jun 11, 2015 at 20:15

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

beeba Over a year ago

Thanks for answering. Sorry, I was unclear - please see the edit.

Collectives™ on Stack Overflow

Changing Range in VBA to Select First Row of Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related