EXCEL FUNCTIONS Custom range and VBA CODE

[Excelday]

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

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