Matrix Creation !

  • Thread starter Thread starter daniroy
  • Start date Start date
D

daniroy

Hello there, and thank you again for all the help I am finding here.
I am trying to automatically build a matrix with lines corresponding to
products identified by numbers, and columns corresponding to months.

I do begin with the the line creation. Up to now my code looks like
_________________________________________________________
Sub Create_Volatility_Matrix()

Dim Minimum_Strike As Single
Dim Maximum_Strike As Single
Dim Strike As Single

Minimum_Strike =
Application.WorksheetFunction.Min(Sheets("Export").Range("D01:D1000"))
Maximum_Strike =
Application.WorksheetFunction.Max(Sheets("Export").Range("D01:D1000"))

For Strike = Minimum_Strike - 50 To Maximum_Strike Step 50
Strike = Strike + 50
Sheets("Matrix").Range("B10").Value = Strike
Next Strike

End Sub
______________________________________________________________

Basically, Minimum Value will be something like 3825 and Maximum like
5825.
What a would like to achieve, and do not succeed to, is to go from
Sheets("Matrix").Range("B10"), write in 3825 and then go on the next
line
Sheets("Matrix").Range("B10"), write in 3875 and so on
What I am doing now, is that all values go through cell B10 from 3825
to 5825.
I understand more or less I should add another For or Loop but I do not
really see it ... So as usual help would be really much appreciated and
thanks again!

Best regards everybody
Daniel
 
? Perhaps, if you mean to fill in B10, then B11, B12, etc..

Sub Create_Volatility_Matrix()

Dim Minimum_Strike As Single
Dim Maximum_Strike As Single
Dim Strike As Single
Dim Counter As Integer

Minimum_Strike = Application.Min(Sheets("Export").Range("D01:D1000"))
Maximum_Strike = Application.Max(Sheets("Export").Range("D01:D1000"))

Counter = 0

For Strike = Minimum_Strike To Maximum_Strike Step 50
Sheets("Matrix").Range("B10").Offset(Counter,0).Value = Strike
Counter = Counter + 1
Next Strike

End Sub

HTH,
Bernie
MS Excel MVP
 
Thanks a lot Bernie it is exactly what I was aiming for. Sorry not to
have come back to you earlier but I did not think having a look during
the weekend!

Best regards and thanks again
Daniel
 
Back
Top