The essence of the problem.

In the cell it is necessary to write a formula that summarizes the range of values ​​of the cells. This range may vary. The length is set to a variable, and even the cycle in rows.

For example, I need to calculate the sum of 8 cells and write the result in the 9th cell. And so for 6 lines.

enter image description here

A piece of code:

numberOfStudent = Range("E6").Value For n = 1 To numberOfStudent Cells(9 + n - 1, 3 + lastValue).Formula = "=SUM(" & Range(Cells(9 + n - 1, 3), Cells(9 + n - 1, 3 + lastValue - 1)) & ")" Next n

In the third line, I get a runtime-error '13' error: Type mismatch

  • one
    Try writing Range(Cells(9 + n - 1, 3), Cells(9 + n - 1, 3 + lastValue - 1)).Address - Eduard Izmalkov
  • @ Edward, thank you so much! - Ksenia Terpigoreva

1 answer 1

The expression Range(Cells(9 + n - 1, 3), Cells(9 + n - 1, 3 + lastValue - 1)) returns an object of type Range , and the formula expects a string. In order for this code to work, you must pass the address of the string to the formula, this is done using the Address method of the Range object. As a result, your code should look like this.

 numberOfStudent = Range("E6").Value For n = 1 To numberOfStudent Cells(9 + n - 1, 3 + lastValue).Formula = "=SUM(" & Range(Cells(9 + n - 1, 3), Cells(9 + n - 1, 3 + lastValue - 1)).Address & ")" Next n