#NAME? error

[tryinghard]

Hi I'm getting the worksheet error #NAME? when my formula in VBA gets evaluated.

 
 Cells(x + y + 4, "D").Formula = "=sum(cells(x+1 , 4) : cells(x+y+1 , 4))"
 
 

I know that the error is refering to the word cells in the formula, but I don't know how to make Excel accept a formula that is not in the usual ColRow : ColRow format.

[zmbd]

To Start with:
Did you actually take a look at the formula entered into the cell by the code?
SUM(Cells(x+1, 4) : Cells(x+y+1, 4))
Excel simply doesn't know what the "X" and "Y" values are (and see my little soapbox about strings in functions (^_^)).

You need to either have cells/ranges named as "X" and "Y" in the work sheet or re-write you code so that the "X" and "Y" values are evaluated.

Even then you are going to get the #Name error because "Cells" is not a defined function at the worksheet level.

Now, you've hit upon something that just drives me crazy...
building the string within the command - and it's not your fault because that's how a majority of examples show how to use the command.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.

Because what you are trying to do is little obscure (that is to say... I don't know exactly what you are trying to do) I'll take a stab based on what I think your code was trying to accomplish.

Sub poc1()
    Dim zx As Long
    Dim zy As Long
    Dim zs As String
    '
    'now because I don't know your loops etc.. I just picked a few numbers
    zx = 5
    zy = 10
    '
    'let's just start in the first cell
    Range("A1").Select
    '
    'enter the formula.... the address() returns the string (in this case absolute) to
    ' the referenced cell,
    zs = "=sum(indirect(address(" & zx & "+1,4)):indirect(address(" & zx + zy & "+1,4)))"
    '
    'See the advanatage here... you can add the debug.print to see the resolved string
    Debug.Print zs
    'if you don't have it open, press <ctrl><g> now, and the immediate window will open and
    'you can see the resolved string
    '
    'so for zx=5 and zy=10
    ' in cell D19, =sum(indirect(address(5+1,4)):indirect(address(15+1,4)))
    ' which is the equivalent of =sum($D$6:$D$16)
    ' also changed your "D" to 4
    Cells(zx + zy + 4, 4).Formula = zs
End Sub

[tryinghard]

Hi
Thanks for your input.
I obviously didn't include the reams of code that define the values of x & y that would have just confused things.

I now know what I missed in that piece of Formula code, namely the use of R1C1 and " & x & ".

I prefer to use the column as a letter "D" instead of the column number 4. It makes understanding the code later on a tad easier.

I take your point that building the string first does have its advantages, it does however add more lines and hence more bulk and more code to slog through, but better is always preferable.

Thanks again for your input.

[zmbd]

The extra line of code is really a very trival thing for today's PCs... you could put a few hundred lines in there like the one we're using and the PC wouldn't even blink an eyee.

As for the use of "D" or the numeric is up to you; however, it is USUALLY much better to maintain a consistaint usage. Switching back and forth like that will only confuse things later on... especially for anyone that follows you as the expectation is to use one method of reference.

You can not use the R1C1 and A1 notation at the sametime at the worksheet level. Also, R1C1 notation is falling out of favor for all but legacy compatability and for use in VBA coding.

best of luck
-z