Hi all
I ran into a problem that I cannot solve myself.
I would like to enter a formula in some cells using VBA

Cells(2, 19).Formula = "=IF('2006-1'!I2<>"";'2006-1'!I2;"")"
Where 2006-1 is another sheet in the workbook.

If I just put some simple formula in there is no problem, but the above one gives me the following error.

Application-defined or object-defined error

Any help is appriciated.

Cheers
Karsten

Hi again
I solved my problem.

Sub insertFormula()
    Dim RowNo As Integer
    Dim J As Integer

    RowNo = 2
    For J = 5 To 107 Step 2
        Cells(J, 19).Formula = "=IF('2006-1'!I" & RowNo & ">0,'2006-1'!I" & RowNo & ","""")"
        RowNo = RowNo + 1
    Next J
End Sub

Sweet - I was halfway through typing up the very same solution!!

Mike
EchoVue.com

I was about to say, the formula need to have a quadruple " if it is to be recoginised as a string which is to be embedded into a formula...

"=IF('2006-1'!A2<>"""",'2006-1'!A2,"""")"


But then again looks like you figured it out anyhow. There is an alternative which I prefer...


Cells(J, 19).Formula = "=IF('2006-1'!I" & RowNo & ">0,'2006-1'!I" & RowNo & "," & Chr$(34) & Chr$(34) & ")"

That is, to use the ASCII character code set to specify the Chr$(34) i.e. ", then concatenate this to the rest of the formula. But that is only my preference.

cheers

Matt