How to successfully operate a loop for calculation?

Question

I have to do a calculation on a 100.00 lines, I have to do a loop and declare the variables. But it is impossible to turn the formula to the end, only the first line is filled.

'variable declaration
 Dim ls As Worksheet
 Set ls = ThisWorkbook.Worksheets("BASE2")
 Dim Lrow As Long
 Lrow = ls.Cells(ls.Rows.Count, "A").End(xlUp).Row

 'calculation
 ls.Range("I2:I" & Lrow).FormulaR1C1 = "=TRIM(LEFT(RC[-6],10))"
 Range("I1").Select
 ActiveCell.FormulaR1C1 = "Article sans désignation"
 Columns("I:I").ColumnWidth = 20.71

I would like to turn this formula down to the bottom of the 95,000 lines (which will be variable from one time to the next, that's why I made a loop)

There is no loop in the code you provided. Also read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — erazorv4, May 23 '19 at 08:14
@erazorv4 No the formula : "Lrow = ls.Cells(ls.Rows.Count, "A").End(xlUp).Row" Excuse me, I thought it was a loop, I just started the code. — XDSSIOP, May 23 '19 at 08:18
When using formulas you don't need to loop, The formula is the same in every row, only change is the row it's applying to, right? Then you have it right, Apply the formula to the range and that's it. Also `Range("I1") = "Article sans désignation"` — Damian, May 23 '19 at 08:21
@Damian Yes, but the formula should go all the way down, but then the formula stops at the first line. — XDSSIOP, May 23 '19 at 08:24
That's because `Lrow = 2` check it out on the debug. Your column A has values down to the last row you are intending? — Damian, May 23 '19 at 08:27
@Damian Yes. Everything's okay, so I don't understand. The code does not stop, it continues until the end, there is no error. — XDSSIOP, May 23 '19 at 08:34

score 0 · Accepted Answer · answered May 23 '19 at 08:35

You code is correct and working (see below). I just think that you might have the incorrect column to select the last row. So, here is your code with some debugging information. Maybe this will help you get things working.

'variable declaration
 Dim ls As Worksheet
 Set ls = ThisWorkbook.Worksheets("BASE2")
 Dim Lrow As Long
 Lrow = ls.Cells(ls.Rows.Count, "A").End(xlUp).Row

 MsgBox "Column A is filled up to row " & Lrow & " with data. Therefore," & Chr(10) _
    & "the following range will be used: " & ls.Range("I2:I" & Lrow).Address

 'calculation
 ls.Range("I2:I" & Lrow).FormulaR1C1 = "=TRIM(LEFT(RC[-6],10))"
 Range("I1").Value2 = "Article sans désignation"
 Columns("I:I").ColumnWidth = 20.71

How to successfully operate a loop for calculation?

1 Answers1