Excel VBA - 1004 run-time error, Application or object-defined error

Question

I'm trying to go through a range of cells in a worksheet and write a formula in each one. But I keep getting the error:

Run-time error '1004'

Application-defined or object-defined error

The code looks like this right now:

Sub updateFormulasForNamedRange()
    'Application.Calculation = xlCalculationManual
    'Application.ScreenUpdating = False

    Dim row, col, fieldCount As Integer
    colCount = 13
    RowCount = 60

    For col = 1 To colCount
        For row = 1 To RowCount
            Dim strColCharacter

            If col > 26 Then
                strColCharacter = Chr(Int((row - 1) / 26) + 64) & Chr(((row - 1) Mod 26) + 65)
            Else
                strColCharacter = Chr(row + 64)
            End If

            Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & col & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

        Next row
    Next col

    'Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True
End Sub

It fails at the line where you assign the formula to the cell. I tried to replace the string with just "test" and it works. But it's this string that's not accepted. Even though it's the exact same string that's currently in the formula bar of that exact cell. And the string looks fine to me?

"=IF(Numbers1!$E$1<>0;Numbers1!$A$1;"")"

I don't quite know the difference of all the Formula properties, but I've tried a variant of them and all throw the same error. So what could be causing this error?

Stewbob · Accepted Answer · 2011-07-11 20:59:06Z

5

Your problem is with .FormulaR1C1. This tells the formula to expect a Row Number, Column Number style formula reference, but then you give it an address (Column, Row) style formula.

Change .FormulaR1C1 to .Formula

answered Jul 11, 2011 at 20:59

Stewbob

16.9k9 gold badges67 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kenny Bones Over a year ago

Still, same thing happens. I've also tried FormulaLocal as well. Doesn't seem to do it.

Kenny Bones Over a year ago

I found the error, it was in the string as I thought it must have. Updating the answer

Tiago Cardoso Over a year ago

If it was @Kenny's answer, would be nice to set his answer as 'accepted'.... and then your topic will be marked as answered.

Kenny Bones · Accepted Answer · 2011-07-11 21:21:55Z

3

Error was in the string:

Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & col & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

Should have been:

Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & row & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

Targeting the row, not the column.

answered Jul 11, 2011 at 21:21

Kenny Bones

5,14938 gold badges117 silver badges180 bronze badges

Collectives™ on Stack Overflow

Excel VBA - 1004 run-time error, Application or object-defined error

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related