Results 1 to 7 of 7

Setting dynamic ranges in macros

This is a discussion on Setting dynamic ranges in macros within the Excel Questions forums, part of the Question Forums category; Hi, I am trying to create a macro that will calc an IRR for a given set of cash flows. ...

  1. #1
    New Member
    Join Date
    Nov 2003
    Location
    Boston, MA
    Posts
    4

    Default Setting dynamic ranges in macros

    Hi,

    I am trying to create a macro that will calc an IRR for a given set of cash flows. To get the data I will be using a query that pulls all the dates and cash flows. To be specific the query will pull the fund name (column A), the Quarter Ending date (Column B), and the net cash flow (Column C). However, I will not be using the Quarter Ending date but rather the midpoint date of the quarter (the query cannot be altered to accomodate this) because it is incorrect for the calculation, so I set up an "index" sheet that the macro can use to lookup the quarter ending date and enter in the corresponding midpoint date.

    The problem arises when I try to tell the macro to only perform the lookup for as many quarter ending dates as there are rows in the current worksheet because the query on each fund doesn't always return the same amount of rows due to some funds having more cash flows than others. Basically, I don't want to set a static range for the lookup but would rather have the lookup perform for as many dates as it has to.

    If this makes sense to anybody your help would be greatly appreciated. Let me know if I need to elaborate.

    thanks,
    DC

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Setting dynamic ranges in macros

    Does this demo help ?

    Public Sub GetInfoDemo()
    For Each Fund In Columns("A:A").SpecialCells(xlCellTypeConstants, 3)

    FundName = Fund.Value
    QtrDate = Fund.Offset(0, 1).Value
    CashFlow = Fund.Offset(0, 2).Value

    resp = MsgBox(Fund.Row & " : " & Fund & " : " & QtrDate & " : " & CashFlow, vbOKCancel, "Demo")
    If resp = vbCancel Then Exit Sub

    Next Fund
    End Sub

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  3. #3
    New Member
    Join Date
    Nov 2003
    Location
    Boston, MA
    Posts
    4

    Default Re: Setting dynamic ranges in macros

    Thanks for the help, however, I think I may have been to forthcoming in my previous question. I really just need to know how to set a range in a column that changes with the amount of data. for instance the data will always start in cell A2 and I need it to perform a function until the last cell with data (i.e. A2:A45). the problem is that the last cell isn't always A45, it might be A10 or A50.

    Here is what I have. The problems is in bold. The "Autofill Desitination" command is probably not correct, but I'm not sure what the correct method should be.

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Transaction_Date"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
    "=LOOKUP(RC[-1], [transaction_index.xls]Sheet1!R1C1:R100C1, [transaction_index.xls]Sheet1!R1C2:R100C2)"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("D2:D35")

  4. #4
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,308

    Default Re: Setting dynamic ranges in macros

    This is how I set my macro to find the last row:

    Dim K As Long
    K = ActiveSheet.Range("A65536").End(xlUp).Row
    . blah blah more code here
    .
    .
    Selection.AutoFill Destination:=Range("T2:T" & K)

    HTH
    texasalynn

  5. #5
    New Member
    Join Date
    Nov 2003
    Location
    Boston, MA
    Posts
    4

    Default Re: Setting dynamic ranges in macros

    thanks Texasalynn! that worked perfect. maybe you can also help me with my last problem. at the end of the column in the cell directly below the last cell with data in it, I need to perform another lookup function (ie. the cell where you would enter a sum after a series of numbers in a column). how do i select that cell as the range to enter the function into, as it will change from calc to calc.

    thanks again,
    DC

  6. #6
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,308

    Default Re: Setting dynamic ranges in macros

    Again from a macro I have this is what I use

    Range("M" & K + 1).Select
    ActiveCell.Formula = "=SUM(M2:M" & K & ")"

    Change the reference to your correct cells and the formula to what your needs are.

    HTH
    texasalynn

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Setting dynamic ranges in macros

    danmoparman

    I understood what you wanted. If you had tried my code you would have seen that it did loop to the end of your data... no matter in what row you data ended.

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com