0

I am writing a code in VBA where I'm trying to fill some cells with a formula while the cell right next to it is different from "". However, my loop never ends and when I interrupt it, it shows me that it's running (or stucked) at the addition of the iterator (sorry for the bad english).

Here's what I wrote so far:

While Cells(i, 4) <> ""
    Cells(i, 5).FormulaR1C1 = "=IFERROR(IF(RC[-3]=R[-1]C[-3],"""",IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3],RC[-3]=R[5]C[-3],RC[-3]=R[6]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[6]C[-1])/SUM(RC[-2]:R[6]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3],RC[-3]=R[5]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[5]C[-1])/SUM(RC[-2]:R[5]C" & _
    "[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3],RC[-3]=R[4]C[-3]),SUMPRODUCT(RC[-2]:R[4]C[-2],RC[-1]:R[4]C[-1])/SUM(RC[-2]:R[4]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3],RC[-3]=R[3]C[-3]),SUMPRODUCT(RC[-2]:R[3]C[-2],RC[-1]:R[3]C[-1])/SUM(RC[-2]:R[3]C[-2]),IF(AND(RC[-3]=R[1]C[-3],RC[-3]=R[2]C[-3]),SUMPRODUCT(RC[-2]:R[2]C[-2],RC[-1]:R[2]C[-1])/SUM" & _
    "(RC[-2]:R[2]C[-2]),IF(RC[-3]=R[1]C[-3],SUMPRODUCT(RC[-2]:R[1]C[-2],RC[-1]:R[1]C[-1])/SUM(RC[-2]:R[1]C[-2]),RC[-1]))))))),"""")" & _
    ""
    i = i + 1
Wend

The formula is working and I am getting the result I expected. However, the application never ends running until e press esc twice. Then when I debug it, the application shows me the "i = i + 1" in yellow.

How can I fix it?

diogoferreira.ac
  • 1
  • Normally one doesn't need a loop to write a formula. Can you explain the setup of column D? Is it completely filled till a certain row, or are there gaps in the data? – BigBen Dec 21 '22 at 21:06
  • I need a loop because I'm copying data from a differente workobook. When it's pasted, then I need to calculate an average (as in the formula above). About the column D, it contains data in format of number. There are no gaps till the end of the table. – diogoferreira.ac Dec 21 '22 at 21:11
  • 2
    You don't need a loop to write a formula to a multi-cell range, i.e. something like `Range("E2:E500").FormulaR1C1 = "yourformula"` works perfectly well. By the way, a `While...Wend` loop is old-fashioned / deprecated. [Find the last cell](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) in column D, then use its `.Row` to figure out the range in column E you need to write the formula to. – BigBen Dec 21 '22 at 21:12
  • The thing is that before a use this formula I copy some data from a different workbook. This data I copy doesn't have the same range/size everyday. – diogoferreira.ac Dec 21 '22 at 21:15
  • That's why you find the last row *dynamically* (see the link above). Then use something like `Range("E2:E" & lastRow).FormulaR1C1 = "yourformula"`, where `lastRow` is a variable. – BigBen Dec 21 '22 at 21:15

0 Answers0