Can't create a formula string

English
,
#1

I’m trying to write a formula into a series of cells. The formula is the same each time – the sum of a column of cells – except that the column number differs each time.

Here is my code (excluding some dim definitions in previous blocks):

Dim ColChar as string
Dim RangeFormula as string

For BColumnNum = BEndColumn to BEndColumn-8 step -1
ColChar=CHR(ColumnNum-64)
RangeFormula=""=sum("&ColChar&“4:”&ColChar&BEndRow+2")""
BufferCell=AssetSheet.getCellByPosition(ColumnNum,BEndRow+2)
BufferCell.setFormula(RangeFormula)
Next BColumnNum

This generates: “BASIC syntax error. Parentheses do not match.”

In the IDE code window, the =sum( part is in green whereas everything else between the first and last quotation marks is in red (ie as if the =sum( is an instruction rather than a string.

Would appreciate help on how to do this.

#2

first : This is the guide - How to use the Ask site? - #6 by erAck

then, attaching a sample .ods + screenshot would be much more clear

last, sounds like using macro is quite overkill :face_with_thermometer:
→ How do I sum the last 12 values in a column?

#3

Hmmm …

  • Always attach an example file in such a case.
  • Show at least for a few examples in the sheet what you actually want to get (in this case formulas). Identify the associated variables used in the code for the example.
  • Realise that there are only 26 columns of a sheet which can be referenced with a single letter.
  • Realise that column numbers for the UI are 1-based while coulumn indices in user code generally are API style, and therefore 0-based.
  • Start writing user code after some basic studies of the means.

Also:

  • The double quotes left and right of the 4: in your posted code are “typographical”.
  • The value of the formula property of a cell is a string, and is therefore shown in a watch window of the Basic IDE with doublequotes which are not part of the string itself.
  • Simply don’t try things this way.
  • Ask for help describing what you actually want to achive.
    There is the wll known XY problem.
#4

Try

#5

Thanks for the suggestion.
This gives me “Basic syntax error” with ColChar& highlighted.

#6

Well, seems nobody read my comments above

formula= "=sum(" & ColChar & "4:" & ColChar & BEndRow+2 & ")"

The so-called typographical doublequotes look like ordinary ones in “Preformatted text”.
Of course, this hokum should generally be omitted by the editor of the forum. Nobody needing “typographical” here.
“O my!” : straight doublequotes.
“O my!” : “typographical” doublequotes in disguise.

#7

Thanks for the advice. Trying to act on it here:

In the attached file, columns K through R contain cells showing numerical amounts. What I’m trying to achieve is to write the formula =sum(K5:K9) into row 11 (only for each column K to R). In the attached file, the desired result is highlighted in yellow.

In the code that I showed, BEndRow would be row 9. The For…Next is supposed to loop backwards from column R to column K, on each iteration writing the formula into row 11.

AskLib.ods (23.3 KB)

#8

Thanks, I didn’t know there was a distinction between typographical and straight double quotes. Interesting!

Your syntax worked, I’m happy to say. Alas, it didn’t have the result I had hoped for (almost certainly a fault in my coding). It wrote to Column A BEndRow + 2 this formula:
=SUM(￀4:￀30))

Back to the drawing board!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.