code does what its supposed to but errors out at the end. Dont know why

[dmcgov]

here is my code (what it does it create a formula thats very long).

It works (meaning that it puts the correct formula in a cell) but error's out at the very end.

Please Login or Register  to view this content.

here is what the formula looks like when its done:

Please Login or Register  to view this content.

as you can see it's very long. so the macro works by putting the formula in E66, however in the visual basic editor it complains about this line:

ActiveCell.FormulaR1C1 = a

any thoughts about what is causing this line to error out?

the error is "run time error '1004': Application defined or object defined error"

[AlphaFrog]

The formula uses A1 refernce style and not R1C1. Try this...

Range("e66").Formula = a

[Winon]

Hello dmcgov,

Try by just removing "ActiveCell.FormulaR1C1 = a" from your Code.

Regards.

[dmcgov]

alpha, i tried what you suggested but it errors out on the Range line.

anything else that i can try?

[AB33]

Replace

Range("e66").Select
ActiveCell.FormulaR1C1 = a

with

Range("e66")=a

[dmcgov]

AB33, same error, it fails on the last line.

[Winon]

@AB33,

I have already tested that to no avail.

Hi dmcgov,

I notice that your Workbook contains about 57 or more Sheets. In order for us us to properly access your issue please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Regards,

[AB33]

No way it fails unless the find function returns nil (False)

Put this line above line and see what the message says

MsgBox a

[Winon]

@AB33,

Still, no joy.

[dmcgov]

i already have the "msgbox a" in my code. what it shows is the formula with one hiccup. let me do a screen snapshot, maybe that will help.

VBerror.jpg

Note that "='Control" is on one line by itself and the rest is on a second line. but when i check cell E66, the formula shows up there the way that its supposed to.

any thoughts?

[Winon]

Hello dmcgov,

Sorry, but I will not be able to assist further, without seeing a sample Workbook, as requested in my last post to you.

Regards.

[AB33]

I am confused too.
If the find function is correct, it returns a static value. You are merely copying a static value to E66. Unless the text exceeds 255, I can not see what the issue is. You can not simply assign a value you extract from a find function into another formula in range. Please attach a sample to see what the error is

[dmcgov]

ok. i stripped down the file (it was 7Mb) and got it under 1mb.

so open file, go to "Control budg + Approved COS", examine cell E66. It has the stripped down formula in it. so go ahead and delete the contents of that cell and then run "test". whatever i did to strip down the file now does not copy the formula to E66. but the error will still show up.

VBerror2.jpg


msgbox.jpg

[AB33]

I need to look at the code again, but removing this line appears to work.
The code keep picking up this line 'a = a + "='Control_Budget'!E66+"
and this line is not valid as it has "="

Please Login or Register  to view this content.

[AB33]

I need to look at the code again, but removing this line appears to work.
The code keep picking up this line 'a = a + "='Control_Budget'!E66+"
and this line is not valid as it has "="

Please Login or Register  to view this content.

[dmcgov]

nah i fixed it. was wondering if the last "+" of the string was causing problem so i changed my code like so:

Please Login or Register  to view this content.

now it works like it was intended.