Recurring revenue and cost

[Harley]

I am trying to write a function that can be placed into a cell, but have it generate a recurring revenue or cost stream in other rows and columns. The function should look like:

BusinessModel(curRevenuePerMonth, curCost, intNumberMonths)

The function should place the curRevenuePerMonth value into a row a specific number of rows away but in the same column for intNumberMonths rows. curCost should be placed in a different row in the sheet only once in a cell a specific number of rows away (different than revenue) but in the same column as the function.

I tried the following code, but I keep getting value errors in the cell with the formula:

Function BusinessModel(RevenuePerMonth As Currency, Cost As Currency, NumberMonths As Integer)
Dim SheetRange As Range
Dim wks As Worksheet
Dim ThisCell As Range
Dim TotalRange As Range
Dim FirstColumn As Integer
Dim intI As Integer
Dim intR As Integer
Dim intC As Integer
intR=-54
intC=-34
Set wks = Worksheets("ThisWorksheet")
ActiveCell.Offset(0, 0).Select
With wks
For intI = 0 To NumberMonths - 1
ActiveCell.Offset(1, intI+intr) = RevenuePerMonth
ActiveCell.Offset(2, intI+intc) = Cost
Next
End With
End Function

Any ideas?

Harley

 

[NickHK]

Harley,
A worksheet function cannot change its environment i.e. values in other
cells. It can only return a value to that cell. So this will not work.

NickHK

I am trying to write a function that can be placed into a cell, but have it
generate a recurring revenue or cost stream in other rows and columns. The
function should look like:

BusinessModel(curRevenuePerMonth, curCost, intNumberMonths)

The function should place the curRevenuePerMonth value into a row a specific
number of rows away but in the same column for intNumberMonths rows.
curCost should be placed in a different row in the sheet only once in a cell
a specific number of rows away (different than revenue) but in the same
column as the function.

I tried the following code, but I keep getting value errors in the cell with
the formula:

Function BusinessModel(RevenuePerMonth As Currency, Cost As Currency,
NumberMonths As Integer)
Dim SheetRange As Range
Dim wks As Worksheet
Dim ThisCell As Range
Dim TotalRange As Range
Dim FirstColumn As Integer
Dim intI As Integer
Dim intR As Integer
Dim intC As Integer
intR=-54
intC=-34
Set wks = Worksheets("ThisWorksheet")
ActiveCell.Offset(0, 0).Select
With wks
For intI = 0 To NumberMonths - 1
ActiveCell.Offset(1, intI+intr) = RevenuePerMonth
ActiveCell.Offset(2, intI+intc) = Cost
Next
End With
End Function

Any ideas?

Harley