Need to Convert Formula R1C1 into A1-style but the Formula String exceeds 255 characters

Hi - I have run into a problem where I am converting a formula in R1C1 format to A1-style. I am using a formula conversion function that works fine as long as the length of the original formula string is less than 256 characters.
For example,
Original formula: "=R4C5*R7C5+R4C6*R7C6+R4C6*R7C7...." (Formula Length < 256)
Converted formula "=$E$4*$E$7+$F$4*$F$7+$G$4*$G$7...." (Formula conversion works fine)

But when the code gets to Row 10 and above, the formula string length exceeds 256 characters and the resulting value in my cell is "#Value". Here is a module of code where I have replaced most of the For-Next variable counters with constants. Is there a better way to generate the formula where I do not have to convert from R1C1-style to A1-style?

Please Login or Register  to view this content.

Hi JakobShavn,
Thanks for your suggestion. I will look up the SUMPRODUCT() formula and see if this will be an alternate way to generate this long formula. - VBA_Gary

A fix has been found.
The problem was that I started using the R1C1-style format rather than A1-style format for building my formula. The easier way to do it was to use the ADDRESS object as shown below. Since my formula used both relative and absolute addressing, the Evaluate(ADDRESS) method allowed me to create the formula using the 4 different options.

Thanks to those that added their comments.

Please Login or Register  to view this content.

@ VBA_Gary

Based on your last post it seems that you solved your issue but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Thanks Cutter for marking this as SOLVED. I looked around the website for the way to mark it as solved but did not find it. I now see it under the Thread Tools tab. Thanks for your help. - VBA_Gary