Excel VBA: Application-defined or object-defined error when setting a cell formula

1
votes

I'm new here.

I'm trying to set the formula of a cell to a given string as below, but am getting a run-time error '1004': Application-defined or object-defined error. The cell is on the active sheet and I can set the formula to blank in the immediate window but I cannot set it to the formula below without hitting the error.

Cells(47, i).Formula = "=('sheetName'!$C$105-SUM(OFFSET($C$47,0,0,1," + _
    CStr(i - 3) + ")))/(COLUMN($N$47)-COLUMN(" + _
    CStr(i - 1) + "$47))"

I'm wondering if there is some special character restrictions on the string?

1
vbaexcel
something wrong with this part: COLUMN(" + CStr(i - 1) + "$47))" because as I see CStr(i - 1) will return number – Dmitry Pavliv
Nevermind! Rookie-mistake. I have the second use of CStr above sending in a number as a cell reference. I set it to Col_Letter(CLng(i - 1)) instead and it works just fine. Col_Letter is a function which returns the letter value of the column number given. – Sujikahalwa
Thanks simoco, you're absolutely right. – Sujikahalwa

1 Answers

2
votes

You are getting an error because of this

COLUMN(" + CStr(i - 1) + "$47))"

The cell address has to be like A1 i.e a Letter and then the Number.