vba- how to use "trunc" in a vba.

[montecarlo2012]

Hello.

Sub RwData(rng As Range, col As Integer)
Dim Dn As Range
For Each Dn In rng
With Application
    Dn.Offset(, -5) = .Max(Dn.Resize(, .CountA(Dn.Resize(, col)))) 'column H or 8 - is minus 5 from 13 or column M
    Dn.Offset(, -3) = .Average(Dn.Resize(, .CountA(Dn.Resize(, col))))  'this is colum J or 10 [or -3 from 13]
    Dn.Offset(, -2) = Dn - .Average(Dn.Resize(, .CountA(Dn.Resize(, col)))) 'column K or -2 from 13
End With
Next Dn
End Sub

I tried to use trunc here but do not work

Dn.Offset(, -3) =.trunc( .Average(Dn.Resize(, .CountA(Dn.Resize(, col)))))

what I am doing wrong.
thank you for reading.

 

[Rick Rothstein]

You do not need to call out to Excel's TRUNC function... VBA has an equivalent built-in function named Fix that works the same.

 

[montecarlo2012]

Rick Rothstein Thanks.​

Sub RwData(rng As Range, col As Integer)
Dim Dn As Range
For Each Dn In rng
With Application
   
    Dn.Offset(, -5) = .Max(Dn.Resize(, .CountA(Dn.Resize(, col)))) 'column H or 8 - is minus 5 from 13 or column M
    Dn.Offset(, -3) =.fix .Average(Dn.Resize(, .CountA(Dn.Resize(, col))))  'this is colum J or 10 [or -3 from 13]
    Dn.Offset(, -2) = Dn - .Average(Dn.Resize(, .CountA(Dn.Resize(, col)))) 'column K or -2 from 13
End With
Next Dn
End Sub

I got debug message.

 

[montecarlo2012]

I am using 2010 excel windows 7

 

[montecarlo2012]

Sub RwData(rng As Range, col As Integer)
Dim Dn As Range
For Each Dn In rng
With Application
   
    Dn.Offset(, -5) = .Max(Dn.Resize(, .CountA(Dn.Resize(, col)))) 'column H or 8 - is minus 5 from 13 or column M
    Dn.Offset(, -3) = .Fix(.Average(Dn.Resize(, .CountA(Dn.Resize(, col)))))  'this is colum J or 10 [or -3 from 13]
    Dn.Offset(, -2) = Dn - .Average(Dn.Resize(, .CountA(Dn.Resize(, col)))) 'column K or -2 from 13
End With
Next Dn
End Sub

 

[bebo021999]

Fix is VBA function, not worksheet function then remove the "."
Dn.Offset(, -3) = Fix(.Average(Dn.Resize(, .CountA(Dn.Resize(, col)))))

 

[montecarlo2012]

Thank you bebo,
now I fix the rest

Sub RwData(rng As Range, col As Integer)
Dim Dn As Range
For Each Dn In rng
With Application
   
    Dn.Offset(, -5) = .Max(Dn.Resize(, .CountA(Dn.Resize(, col)))) 'column H or 8 - is minus 5 from 13 or column M
    Dn.Offset(, -3) = Fix(.Average(Dn.Resize(, .CountA(Dn.Resize(, col))))) 'this is colum J or 10 [or -3 from 13]
    Dn.Offset(, -2) = Fix(Abs(Dn - .Average(Dn.Resize(, .CountA(Dn.Resize(, col)))))) 'column K or -2 from 13
End With
Next Dn
End Sub