EXCEL FUNCTIONS Custom range and VBA CODE

Excelday

New Member
Joined
Jan 9, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Question 1: In Excel, I want to use the XIRR formula on a discontinous range, e.g. I have dates in cells B3:F3 and cash flows in cells B6:F6. I need to put "-" before the first cash flow, but the formula below does not work = XIRR((-B6,C6:F6),B3:F3) So the only way I can think of is to copy all the cash flows and put - against the first one. Can I use XIRR without having to copy the numbers so that the first cash flow is negative? Please see
2.png


Question 2: I want to use goal-seek in VBA that loops through a series of data in rows. Goal-seek can fail sometimes but if a reasonably close start value is provided then it is more likely to converge. This is the reason I use XIRR function to provide a close estimate. I have written a macro to do this but it copies formulae in Excel and then paste results, so it runs slow when data is large. How can I amend this macro so that all this operation is done in VBA (including calling Excel formula) and only the final results from goal-seek are pasted into cells A6:A8?

```
Sub Macro3()
'
' Macro3 Macro
'

'
Range("B11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=-R[-5]C"
Range("B11").Select
Selection.Copy
Range("B11:B13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-5]C"
Range("C11").Select
Selection.Copy
Range("C11:F13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A11").Select
ActiveCell.FormulaR1C1 = "=XIRR(RC[1]:RC[5],R3C2:R3C6)"
Range("A11").Select
Selection.Copy
Range("A11:A13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G6").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(RC[-4]:RC[-1],(1+RC[-6])^(-R4C[-4]:R4C[-1]))-RC[-5]"
Range("G6").Select
Selection.Copy
Range("G6:G8").Select
ActiveSheet.Paste
Application.CutCopyMode = False

For Row = 6 To 8
Range("G" & Row).GoalSeek Goal:=0, ChangingCell:=Range("A" & Row)
Next Row


End Sub
```
Cross-posted in: Excel function with custom range in VBA
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

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
Top