Sharing Interest Dynamically

[respuzy]

Hello Experts

Good day, please in the sheet attached, i need to share interest in column J (with formula example already provided in the column J2:J10), i need to replicate this dynamically in that column per each valuation date data by taking the column H "NAV after Redemption" dividing it by the total of that valuation date data and multiply it by the corresponding distributable returns in column I.

for instance for valuation date 30-jun-16 , in column I, it should calculate 49.95 (Net NAV)/1529.18 (sum total) *27.44 (distributable return)
For valuation date 31-jul-16, in column I, it should calculate 77.34 (Net NAV)/2342.36 (sum total) *50.64 (distributable return)

please revert for further clarification of need be. thanks

[SamT]

Is this school homework? We have an almost identical question in
http://www.vbaexpress.com/forum/showthread.php?62049-Distribute-Numbers-Evenly-With-Conditions

[respuzy]

Hahaha, No Boss, its not homework, far from that, its a file i am building to automate my process. i would be grateful if i can be assisted though. i will be happy to explain further if need be

thanks

[SamT]

to automate my process.
Tell us about the process. We probably have more ideas about automating it than you do.

[respuzy]

i tried to come out with this which resolved my issue. thanks so much

Sub InterestSharing()
Dim x, y, z As Long
x = Sheet5.Cells(Rows.Count, 8).End(xlUp).Row
y = Sheet5.Cells(Rows.Count, 9).End(xlUp).Row
Sheet5.Range("j" & y & ":j" & x - 1).Formula = "=H" & y & "/$H$" & x & "*$i$" & y

[SamT]

:thumb