VBA: repeat a formula a number of times

Ok this is great thanks for your help and explanation.
i am really sorry for asking too many questions but I am still facing some troubles:

My problem now is the following in the sub NouveauxXY():

I need my code to perform calculation for the columns I,J,K,L,M,N.
what i need is let's say:
for i = 2 to 17,
to lookup for values in columns A,B and C for the rows 2 to 53 and perform the necessary calculations
(the 53 is the number of occurrences of the value -40)

then
for i = 18 to 33 (there is a step of 16 for each time)
to lookup for values in columns A,B,C for the rows 54 to 54+62
(the 62 is the number of occurrences of the value -38)

etc...
for the 20 different values of Z





I did an example for the first two values (-40 and -38) but manually. I need it to do the calculation for
(I will move my code to module as soon as I find a solution for this problem)

Thanks again for all your support

---

'problem starts here

'for -40

For i = 2 To (nb + 2) '* (nbofcontours - 1)

tot = ThisWorkbook.Sheets("Temp1").Cells(3, 2)

'x1
Cells(i, 11) = Application.WorksheetFunction.VLookup(Cells(i, 8), Range(Cells(i, 1), Cells(tot + 1, 1)), 1)
'z
Cells(i, 10) = ThisWorkbook.Sheets("Temp1").Cells(3, 1)

'y1
Cells(i, 12) = Application.WorksheetFunction.Index(Range(Cells(2, 2), Cells(tot + 1, 2)), Application.WorksheetFunction.Match(Cells(i, 11), Range(Cells(2, 1), Cells(tot + 1, 1)), 0))

'y2
Cells(i, 14) = Application.WorksheetFunction.Index(Range(Cells(2, 1), Cells(tot + 1, 2)), Application.WorksheetFunction.Match(Application.WorksheetFunction.VLookup(Cells(i, 8), Range(Cells(2, 1), Cells(tot + 1, 1)), 1), Range(Cells(2, 1), Cells(tot + 1, 1)), 0) + 1, 2)

'x2
Cells(i, 13) = Application.WorksheetFunction.Index(Range(Cells(2, 1), Cells(tot + 1, 1)), Application.WorksheetFunction.Match(Cells(i, 14), Range(Cells(2, 2), Cells(tot + 1, 2)), 0))

'y par thales
Cells(i, 9) = (Cells(i, 8) - Cells(i, 11)) * (Cells(i, 14) - Cells(i, 12)) / (Cells(i, 13) - Cells(i, 11)) + Cells(i, 12)

Next


'for -38

For i = nb + 3 To (nb + 1 + nb + 2) '* (nbofcontours - 1)
tot = ThisWorkbook.Sheets("Temp1").Cells(3, 2)
tot1 = ThisWorkbook.Sheets("Temp1").Cells(4, 2)

'x1
Cells(i, 11) = Application.WorksheetFunction.VLookup(Cells(i, 8), Range(Cells(tot + 1, 1), Cells(tot + 1 + tot1 + 1, 1)), 1)
'z
Cells(i, 10) = ThisWorkbook.Sheets("Temp1").Cells(4, 1)

'y1
Cells(i, 12) = Application.WorksheetFunction.Index(Range(Cells(tot + 1, 2), Cells(tot + 1 + tot1 + 1, 2)), Application.WorksheetFunction.Match(Cells(i, 11), Range(Cells(tot + 1, 1), Cells(tot + 1 + tot1 + 1, 1)), 0))

'y2
Cells(i, 14) = Application.WorksheetFunction.Index(Range(Cells(tot + 1, 1), Cells(tot + 1 + tot1 + 1, 2)), Application.WorksheetFunction.Match(Application.WorksheetFunction.VLookup(Cells(i, 8), Range(Cells(tot + 1, 1), Cells(tot + 1 + tot1 + 1, 1)), 1), Range(Cells(tot + 1, 1), Cells(tot + 1 + tot1 + 1, 1)), 0) + 1, 2)

'x2
Cells(i, 13) = Application.WorksheetFunction.Index(Range(Cells(tot + 1, 1), Cells(tot + 1 + tot1 + 1, 1)), Application.WorksheetFunction.Match(Cells(i, 14), Range(Cells(tot + 1, 2), Cells(tot + 1 + tot1 + 1, 2)), 0))

'y par thales
Cells(i, 9) = (Cells(i, 8) - Cells(i, 11)) * (Cells(i, 14) - Cells(i, 12)) / (Cells(i, 13) - Cells(i, 11)) + Cells(i, 12)



Next


End

---