#value! error while using Range object in VBA/Excel

Question

I am trying to write my first vba macro for excel 2010. It needs to do some long calculations for 200,000 rows and needs to populate 2 adjacent cells on the active row. The following VBA function is a crude first attempt to accomplish the above. Problem is while executing the macro, I get #value! error. If I remove "cells" and .Value lines, it works fine and populates the activecell with appropriate value. What am I doing wrong?

Appreciate the help very much.

Public Function someCalculation() As Integer

    Dim r As Range
    Dim nrow, ncol As Integer

    nrow = ActiveCell.Row
    ncol = ActiveCell.Column

    r = Cells(nrow, ncol + 1)
    r.Value = nrow * 100 + ncol

    someCalculation = nrow * 1000 + ncol

End Function

You cannot set the value of any cell in the workbook other than the cell in which the User Defined Function (aka UDF) resides. Your requirement that it 'needs to populate 2 adjacent cells on the active row' cannot be accomplished. — user4039065

grominet grominet · Accepted Answer · 2015-05-31T17:39:52

"Cells" is an object, you must use 'Set' with any object to assign it.

But it's not possible to modify another cells than the one with function.

Edit : remove wrong code

    Set r = Cells(nrow, ncol + 1)

#value! error while using Range object in VBA/Excel

1 Answers