ansaurus

Question

problems with VBA code application or object error

Answer 1

A:

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)"

Tomalak 2010-08-10 09:23:49

I tried this in the first place, however it produced same error someone advised me to add quotes:=IF(and("'Criterion " "'Criterion " "'Criterion " 0)However my VB was adding a space between first " and ' making content after the latter appearing as a comment. I tried to escape it using chr(34). What could be wrong then? I tried to check your code, but excel says that the formula contains an error.thx

niuchu 2010-08-10 10:00:47

@niuchu: My VBA editor, where I just tested it, accepts this as legal code that produces exactly the string I show in the answer. And Excel accepts that string as a perfectly valid cell formula. You are doing something wrong. Hint: Maybe you should check what `cellAdress` and `i` contain in your case.

Tomalak 2010-08-10 10:14:05

Answer 2

A:

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.

niuchu 2010-08-10 10:41:22

ansaurus

tags:

views:

answers:

problems with VBA code application or object error

related questions