Excel 2007 VBA error 1004

I am trying to insert a formula that checks for a 'Y' in a cell and I get the above error so the syntax must be wrong: -
ActiveCell.Formula = "=IF(AA3.value='Y' etc.

Can anyone help?
HKFueyAsked:
Who is Participating?
 
regmigrantConnect With a Mentor Commented:
I think that is a problem with using single quote vs double quote - excel needs double quotes in the formula

to 'escape' a double quote you need a double double quote :-

""""

so:
"=IF(AA3=""""Y"""",""""Y"""",""""""""')"

or set a string for qt = """" and then concatenate the formula - because its difficult to keep count.

"=if(aa3=" & qt & "Y" & qt & "," & qt & "Y" & qt & "," & qt & qt & ")"

not an uncommon problem - and very messy

http://stackoverflow.com/questions/216616/how-to-create-strings-containing-double-quotes-in-excel-formulas
0
 
regmigrantCommented:
possible reasons:-
- you have the sheet protected
- your formula is incorrect (missing bracket or parameter)

Can you post an example - or at least complete  code lines  above and below
0
 
HKFueyAuthor Commented:
OK full formula: -
ActiveCell.Formula = "=IF(AA3.value='Y',(((V3+I3+J3+K3)-(L3+M3+N3+O3))*-1)+C3,((V3+I3+J3+K3)-(L3+M3+N3+O3))*-1)"

It was this: ((V3+I3+J3+K3)-(L3+M3+N3+O3))*-1

The iff statement breaks it so 'AA3.value='Y'' must be wrong
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
regmigrantCommented:
the formula within the " must be a valid excel formula on it own - ie: you should be able to cut it from the code and put it in a cell - if you do that the error generated will highlight what is wrong with the formula.

a couple of guesses:

- if you are looking at the value in AA3 then use it as though in the spreadsheet:
 = "=if(AA3 = 'Y'.....


- if aa3.value is an object or variable from within the script and you want to use its current value:
 "=if(" & aa3.value & "='Y'....
0
 
HKFueyAuthor Commented:
OK to simplify, this still gives the error: -

ActiveCell.Formula = "=IF(AA3='Y','Y','')"
0
 
HKFueyAuthor Commented:
Got there nearly same time! Answer is also here: -
http://www.ozgrid.com/forum/showthread.php?t=42515
0
 
HKFueyAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.