CODEWITHSUNDEEP
vbaexcelrange
beeba
beeba

Reputation: 432

Changing Range in VBA to Select First Row of Table

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

Upvotes: 1

Views: 2612

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 0

Related Questions