Excel Cell formula issue

[arkrish]

Hi,

I am facing problem with cell formula to capture in macro, complete formula not populating while recording macro.

Lines are disconnecting, (Ex: In code one of the lines has "CONCATENATE(LEFT(R2,33)" , it's coming as "CONCAT"& _" but in next line there is no continuation)

Attached is the formula and output macro code. Is there any limitation on cell formula lines? even if i changed continuously without spaces it's not working.

I am using 2010 excel, please help me on this.

Thanks

[nathansav]

You've envlosed your things like carriage return chr(10) in inverted commas so they are being treated as a string rather than chr(10)

Also, i dont think carriage returns are advisable in formulae.

[arkrish]

Thanks for the quick reply Nathan, i have removed all the spaces, change cell wrap text and tried but no luck...can you please advise how to resolve it or is there any other short way to get that formula logic?

[pareshj]

Hi arkrish,

There is limitation on formula lines, but your formula size is under max limit.
Check the below link for the same :
http://office.microsoft.com/en-in/ex...010073849.aspx

Also, please provide an excel file in which you got the formula, dont provide formula in txt file.


Regards,
Paresh J

[pareshj]

Hi,

Just found out that in your ActiveCell.FormulaR1C1 =, please check the fifth, sixth line of the txt file :
which contains Concatenate function is not proper :
1) RC[12],19
2) LEFT(RC[12],33)


Regards,
Paresh J

[arkrish]

Hi Paresh,

Attaching the excel sheet to give more detailed, still it's not working.

Thanks,
Krish

[pareshj]

Hi,

Please provide Excel with macro code which is creating issue.


Regards,
Paresh J

[arkrish]

Attached the sheet with macro..

[arkrish]

Hi anyone help on this issue?

[pareshj]

Hi Arkish,

My apologies that I could not reply you earlier.
Check the attached macro, I have done is I have taken formula from cell E5 and use the same formula for cell E7. Check whether this is what you want:


Attachment :
Book3.xlsm


Regards,
Paresh J

[arkrish]

Hi Paresh,

I dont see any formula in cell E7, can you please upload right excel with macro enabled?

Thanks

[pareshj]

Hi,

You need to run the macro after which formula of E5 will be get visible in E7. RUN THE MACRO...


Regards,
Paresh J

[arkrish]

when i am recording my formula, again its giving the same error, start record and can you please write/copy paste then stop macro record, then i am getting the same error...missing some code.

Can you please take cell formula and tried to record formula in macro...

[arkrish]

Giving example...

I have column B, C has 20k record values, and want to insert formula in E (i.e. active cell (first cell)), so now write formula in E column, apply the same formula for all cells in E column.

This is what i need and i want to automate that through macro, so when i will do it in Excel it works without any issue.

but when i record macro to generate the code for that, it's not giving complete formula and it's giving error even i am trying to execute through code.

[arkrish]

Giving example...

I have column B, C has 20k record values, and want to insert formula in E (i.e. active cell (first cell)), so now write formula in E column, apply the same formula for all cells in E column.

This is what i need and i want to automate that through macro, so when i will do it in Excel it works without any issue.

but when i record macro to generate the code for that, it's not giving complete formula and it's giving error even i am trying to execute through code.