Solved: Code for vba to replace below formula

[p45cal]

…there is part which i don't understand on that can you please explain it to me so that i later use on other system .

ModuleData = Application.Transpose(.Cells(theRange.Row, ModuleColmNo).Resize(rowMax))

what this is actually doing before the calculation.

ModuleData is an array (in memory) of the values in R35:R419 of your sample file.
Taking the innermost parentheses first:
.Cells(theRange.Row, ModuleColmNo)
The cells property, when followed by numbers, represents a single cell:
cells(row no., column no.)
theRange.row is the top row of the range passed to the sub blah and is 35.
ModuleColmNo is 18, assigned earlier in the code.
So we have
cells(35,18), which is R35.
This range (single cell) is resized with .Resize(RowMax)
RowMax is 385, assigned earlier in the code.
The resize property, like the cells property, uses two numbers, rows and columns:
Resize(RowSize, ColumnSize)
In this case there's only one number, so it's the first one, RowSize.
So now the range is R35:R419
Finally, the Application.transpose bit; With it in place the line produces an array which looks like:
http://www.box.net/shared/u6tizvxn3r
which is a single dimension array, without the transpose, it would look like:
http://www.box.net/shared/zvvsk5r8mf
which is a two dimensional array (I've expanded 3 of the members) which is more difficult to work with than a single dimension array and contains no more information.

ModuleData is then used later, over and over again in the lines:
[vba] ThisModule = ModuleData(rw)
SecondSumif = 0
For i = 1 To rw - 1
If ModuleData(i) = ThisModule Then SecondSumif = SecondSumif + myArray(i, Colm)
Next i
[/vba] and is used to calculate the 2nd SumIf part of the formula, highlighted in blue below:
[vba]=IF($R35="",0,IF(N$33<$O35,0,IF(0<($AA35),MIN(($AA35-SUM(M35:$AF35)),$AB35,SUMIF(Shadow_km_Module,$R35,N$3:N$32)-SUMIF($R34:$R$34,$R35,N$34)),0)))[/vba]in the longest line of thesub which starts:
result = Application…
where the value is held in the variable SecondSumif.

2nd part :
Else
If Cells(33, SheetColm) < Cells(SheetRow, 15) Then
myArray(rw, Colm) = 0

why cells(33, ) and cells( , 15 ) has been used.

As already mentioned above, the Cells property is followed by 2 numbers: row and column. So the 33 represents a row number, and the 15 represents a column number.

It's the vba equivalent of the red part in the formula below:
[vba]=IF($R35="",0,IF(N$33<$O35,0,IF(0<($AA35),MIN(($AA35-SUM(M35:$AF35)),$AB35,SUMIF(Shadow_km_Module,$R35,N$3:N$32)-SUMIF($R34:$R$34,$R35,N$34)),0)))[/vba]The 33 is prefixed with $ so it's an absolute reference (doesn't change), while column 15 (column O) is also prefixed with a $.