Solved

Excel 2007 VBA error 1004

Posted on 2014-03-05
7
614 Views
Last Modified: 2014-03-05
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?
0
Comment
Question by:HKFuey
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39905935
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
 

Author Comment

by:HKFuey
ID: 39905956
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 39905961
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:HKFuey
ID: 39905981
OK to simplify, this still gives the error: -

ActiveCell.Formula = "=IF(AA3='Y','Y','')"
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 39905993
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
 

Author Comment

by:HKFuey
ID: 39905994
Got there nearly same time! Answer is also here: -
http://www.ozgrid.com/forum/showthread.php?t=42515
0
 

Author Closing Comment

by:HKFuey
ID: 39905995
Thanks!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now