Solved: Convert NPV Function into VBA

[mikeoly]

Could anyone help me convert this into VBA code?

=NPV($C$41,$D$71:$H$71)+H94/((1+$C$41)^5)

~~~~~~~~~~~~~~~~~~
Also, does anyone know why I get a 'application defined or object defined error' in the 4th line from the bottom?

Private Sub cbOK_Click()
Dim Scenario As Single


Scenario = Right(ListBox1.Value, Len(Scenario) - (Len(Scenario) - 1))

For r = 1 To ActiveWorkbook.Worksheets("Sheet1").Range("RiskCompile").Rows.Count
For c = 1 To ActiveWorkbook.Worksheets("Sheet1").Range("RiskCompile").Columns.Count

ActiveWorkbook.Worksheets("Sheet1").Range("RiskCompile").Cells(r, c).Value = ActiveWorkbook.Worksheets("Sheet3").Range("R1.&Scenario&").Cells(r, c).Value
Next c
Next r
End Sub

[mikeoly]

And I know the coding is very redundant without any "With" statements, but I used this brute force method to troubleshoot.

[macropod]

Without know what you're expecting in terms of the formula, '=NPV($C$41,$D$71:$H$71)+H94/((1+$C$41)^5)' could be nothing more than:
Range("A1").Value = "=NPV($C$41,$D$71:$H$71)+H94/((1+$C$41)^5)"
The compile error is possibly because:
("R1.&Scenario&")
should be:
("R1." & Scenario)

[mikeoly]

I must be really getting brain dead for coding all day. Thanks for the help Paul!

Mike