problems with VBA code application or object error

I have coded some code in VBA my code breaks at this line raising Application defined or object defined error.

.Formula = "=IF(AND(chr(34开发者_如何转开发) & ' & chr(34) & Criterion " & i & "'!" & cellAdress & ">=1;chr(34) & ' & chr(34) & Criterion " & i & "'!" & cellAdress & "<=4);chr(34) & ' & chr(34) & Criterion " & i & "'!" & cellAdress & ";0)"

I really tried to check what is wrong but it looks fine too me. Please let me know what could be wrong and how to fix it.

Thank you

I think you want

.Formula = "=IF(AND('Criterion " & i & "'!" & cellAdress & ">=1;'Criterion " & i & "'!" & cellAdress & "<=4);'Criterion " & i & "'!" & cellAdress & ";0)"

At least this produces a valid and sensible cell formula, your's does not.

With cellAdress set to "A1" and i set to 10, the result would be:

"=IF(AND('Criterion 10'!A1>=1;'Criterion 10'!A1<=4);'Criterion 10'!A1;0)"

Ok I understand it now. My VBA also doesn't raise any errors until I run. Maybe I'm looking on this code for too long and go crazy. My cell Address contains D18 and i contains 1. I do have a sheet called Criterion 1 and the cell in this sheet has a value of 2 (i also tried when it was empty). Still this error is raised and I don't know what is causing it.