Set cell formula syntax

[JTBorton]

Having trouble getting a cell to accept a formula. I looked at the Microsoft Help example on formulas and tried to follow it, but i'm still having trouble.

Excel 2003

Run-time error '1004'
Application-defined or object-defined error

With wksStabilizers
    For F = 13 To intLastCol
        Ltr = GetColumnLetter(F)
        strFormula = Ltr & "10"
        For G = 11 To intLastRow Step 3
            strFormula = strFormula & "," & Ltr & G
        Next G
        Col = Ltr & "5"
        [highlight].Range(Col).Formula = "= SUM(" & strFormula & ")"[/highlight]
    Next F
End With

I have also tried

    .Columns(Col).Formula = "= SUM(" & strFormula & ")"
    .Cells(5, F).Formula = "= SUM(" & strFormula & ")"
    wksStabilizers.Range(Col).Formula = "= SUM(" & strFormula & ")"
    wksStabilizers.Columns(Col).Formula = "= SUM(" & strFormula & ")"

but they do not work either

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Hi,

What's the value in strFormula?

What's the values in Col?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

How many arguments are coded in strFormula? Excel accepts only 29.

combo

 

[JTBorton]

Skip, Combo

The value of strFormula at failure (which is the first run) is

M10,M11,M14,M17,M20,M23,M26,M29,M32,M35,M38,M41,M44,M47,M50,
M53,M56,M59,M62,M65,M68,M71,M74,M77,M80,M83,M86,M89,M92,M95,
M98,M101,M104,M107,M110,M113,M116,M119,M122

The value of Col depends on the method I was trying to use:
for .Columns(Col) the value is M
for .Range(Col) the value is M5

I count 39 arguments in strFormula so we can identify that as one problem. I guess the solution in this situation is to simply write a function for adding up the sum through a loop.

Now, to answer the original question with absolute clarity, am I using the proper syntax for setting the cell formula, or was that also incorrect?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

What kind of worksheet structure do you have, where you have to sum non-contigulus cells? There's got to be a better way!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

If your code works for permitted numbers of arguments - yes. The only problem could be the string returned by GetColumnLetter(F) if F>26, this can be easily tested in immediate window.

combo

 

[JTBorton]

SkipVought, to see the sheet structure see another thread I actually just now posted,

http://www.tek-tips.com/viewthread.cfm?qid=1565591&page=1

I did not create the sheet, and I do not have time to redesign it. I've just been assigned the task of getting it to run again.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]