 # Changing variable and .Formula giving strange results

Hello everyone.

MacOS Mojave version 10.14.3
LibreOffice Community Version: 7.1.1.2

So, I have the following bit of code, which I will explain:

``````Sub sum_changing_variable_test

Dim i As Long, b As Long
i = 0
b = 1

Formsomme = "=Sum(D" & b &":D9)"

Do Until i = 7 And b = 8

ThisComponent.Sheets.getByIndex(0).getCellByPosition(2,i).Formula = Formsomme

i = i + 1
b = b + 1

Loop

end sub
``````

So basically, I am writing a macro that will put formula SUM(D1:D9) in cell C1, SUM(D2:D9) in C2, etc., all the way to SUM(D8:D9) in C8, thanks to the variables i and b that go up by one in the Do While…Loop. The variable i is for the rows, and b for the first cell in the D column.

However, for some reason, I just get SUM(D1:D9) from C1 to C9.

Therefore, I don’t know why D1 is not going up to D2, D3, etc., and why the formula is inserted into an additional row, when getCellByPosition(2,i), with i going from 0 to 7, means that the results should only appear in rows 1 to 8.

Would anybody know why this is happening ?

Thanks in advance for any potential attempts at explaining why.

Hello,

you never adapt your formula, since you set `Formsomme = "=Sum(D" & b &":D9)" `outside the loop, before the loop starts. This way you set always formula to `= "=SUM(D" & 1 &":D9)"` (i.e. to `=SUM(D1:D9)`) for all iterations. Change your code to:

``````Sub sum_changing_variable_test

Dim i As Long, b As Long
i = 0
b = 1

Do Until i = 7 And b = 8

Formsomme = "=Sum(D" & b &":D9)"
ThisComponent.Sheets.getByIndex(0).getCellByPosition(2,i).Formula = Formsomme

i = i + 1
b = b + 1

Loop

end sub
``````

Note(s):

• I’d adapt the code to reflect that `b = i + 1` and thus simplifying the code.
• You could also use `ThisComponent.Sheets.getByIndex(0).getCellByPosition(2,i).Formula = "=Sum(D" & i+1 & ":D9)"` to make clear, why you need to set `Formsomme` to a new value on each iteration.
• You wrote “means that the results should only appear in rows 1 to 8.” - That’s not correct. it appears on rows 1 to 7 only since condition to run an iteration is checked on the very beginning of the loop. And using `UNTIL` means, that the loop is not performed if `i` equals `7` since in that case `i=7` is `true` and no longer `false` (see also Libreoffice Help - Do…Loop Statement)