Setting dynamic ranges in macros

danmoparman

New Member
Joined
Nov 10, 2003
Messages
4
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top