VBA: repeat a formula a number of times

[dinahajjar]

Hello,

I am trying to program in VBA.
I have the following table:
53 40
62 50
70 60
68 70
68 80
48 90
55 100


I need to return in a third column the following:
- 53 rows in which it returns the value of 40
- 62 rows in which it returns the value of 50
etc...
so I would have something like this:

53 40 40
62 50 40
70 60 40
68 70 40
68 80 ... (to row 53)
48 90 50 (from row 54)
55 100 50 (to row 54+62) etc...



I have attached an example and how I did it but manually for the first two values. I need it to repeat this for as much values as I have in column A & B.


Help please,

Thanks

[AlphaFrog]

Please Login or Register  to view this content.

[Andrew-R]

Give this a try:

Please Login or Register  to view this content.

This line:

Please Login or Register  to view this content.

Determines which column the values are written to (column 5 = E), change that as required.

[dinahajjar]

Thanks a lot Andrew_R and AlphaFrog, this was very helpful and it worked!!

[dinahajjar]

One more question pls:
what if I need it to return the values to sheet2?

[AlphaFrog]

Please Login or Register  to view this content.

[Andrew-R]

With my macro, like this:

Please Login or Register  to view this content.

[dinahajjar]

Thanks a lot again!

[dinahajjar]

Hello again,
could you pls tell me where I went wrong in the attached file?
I need my values to appear in column J.

Thanks

[AlphaFrog]

The "Data" array is a two dimensional array. The column number indices for Data are 1 and 2, and not the same column numbers as on the worksheet e.g. 15 and 16.

Also, put this in a standard code module e.g. Module1 and not the worksheet's code module.

Please Login or Register  to view this content.

[dinahajjar]

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

---