Hello, I am running excel 2013 and get the error 1004 when I try to run my macro. The problem is in the formula highlighted in yellow. I do not know how to fix it.
Hello, I am running excel 2013 and get the error 1004 when I try to run my macro. The problem is in the formula highlighted in yellow. I do not know how to fix it.
Last edited by TheExcelDummy; 06-01-2018 at 03:07 PM.
Welcome to the Forum!
An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.
The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
There is character length limit of 255 for .Formula / .FormulaR1C1 if I recall.
Instead of just using line change "_", try splitting formula string into smaller bits using "&" as well.
If you need help, post the actual code here using # (code tag), rather than posting image.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
The 255 character limit only applies to FormulaArray.
The formula must be wrong but since we can't see all of it, it's hard to correct!
Don
Please remember to mark your thread 'Solved' when appropriate.
Wow! You guys are fast.
I can't attach the file due to personal info but I will layout the code below.
Also, the formula works in normal format. I'm not used to seeing a formula in macro format so that may make a difference.
Here is the code:
Please Login or Register to view this content.
Last edited by 6StringJazzer; 05-31-2018 at 12:32 PM. Reason: code tags
@xlnitwit
Thanks for the info. I almost never use .Formula, .FormulaArray etc in my codes and had thought all shared same property.
@TheExcelDummy
Please edit your post to use code tags.
Your formula is indeed invalid.
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
Thank you, sorry about that.
Thank you, where did I go wrong?
The second line of the VBA for the formula starts with -3, but there is no RC[ in front of that... also the same line ends in RC[ but the next line does not continue that....
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
I fixed it so the "& _" didn't cut out what I needed, is there a rule with the "& _" that I need to work with because I still get a compile error
Please Login or Register to view this content.
Painful formula to work through... Aside from some other small issues in the formula, one big thing on your last post is that the 2nd formula line does not start with opening quotes to continue the string, same with row 3, also check the endings of rows 1 and 2. However, try this ammended version:
Please Login or Register to view this content.
Last edited by Arkadi; 05-31-2018 at 03:21 PM.
Note that you have an if where you check for Instant approved, and then later you use a lower case "i" (instant approved)
The formula will be case sensitive so make sure you double-check for that.
The = operator in an Excel formula is not case sensitive.
Thanks xlnitwit.... My bad then. I always just assumed and never did test that. Appreciate the tip
Thank you so much. What was the determining factor in breaking up the lines. How long until you have to break it up?
I like to break them up so that I don't have to scroll across my monitor... but aside from that there is no rule. The breaking up I did had nothing to do with making it work... it was a matter of fixing the quoting within the string. There was a stray comma somewhere, and on the first line it ended with ""frau" & _
The double quote before frau would make "frau part of the formula, with no quote at the end... since the last quote only completes the string for that row.
It can be tough to put those together in code... I typically suggest that you first make long formulas like that inside a cell to make sure it is written right, then once working, paste into VBA, add " & _ to break up the lines, and then for any " that is part of the formula (so not the new ones you put in just to break up the code into multiple lines), change it to a double ""
P.S. Thanks for the rep, and if the issue is resolved, please remember to mark the thread as solved? Thanks in advance
I originally recorded the macro of my typing in the formula but it broke it up and put the "& _" in for me and cut out some pieces, thus creating the "frau" so I will look out for that next time.
Thank you again.
Last edited by TheExcelDummy; 06-01-2018 at 03:05 PM.
No problem! Glad I could help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks