Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
i am able to read my table using macro -
Sub ReadStraightTable
Set Table = ActiveDocument.GetSheetObject( "CH05" )
For RowIter = 1 to table.GetRowCount-1
For ColIter = 2 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
MsgBox(cell.Text)
Next
Next
End Sub
now i want to calculate irr in table box but im not getting how to pass table values from macro above to the table box properties to calculate irr. any help would be thankful.
No. My question is how the values needs to be stored in a variable.? Loop through all values in a chart and store the values like -120, 200, -100, 200 or like a field .
If you really want to calculate IRR using a macro, maybe you can mix that code you have with this
Sub MG16Aug25
Dim Rng As Range, Dn As Range, Rng2 As Range
Dim x, P, Rs As Double
'Set rng coloumn "A"
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
' First cell in rng = "Principle" Amount +-
'Rng2 = column "A" range starting row 2
Set Rng2 = Rng.Offset(1).Resize(Rng.Count - 1)
'Percentage rate "x" starts as minus value
x = -1
'Loop until Final Value - Last value Is within +- 10% of Last value
Do Until Rs <> 0 And Rs > 0 - (Rng(Rng.Count) * 0.05) And Rs < 0 + (Rng(Rng.Count) * 1.01)
'P = principle
P = Abs(Rng(1))
Rs = 0
x = x + 0.01
'Continally loop through rng until Criterial on "Do loop" Met
For Each Dn In Rng2
'Rs = result at after each period
Rs = (P * (1 + x)) - Dn
'P value increased every loop
P = Rs
Next Dn
'Set P to Zero after each "Do Loop"
P = 0
Loop
x = Format(x, "0.00%")
MsgBox x
End Sub
the values would store like -100
200
-100
like this
hi clever,
i want to read the table and den with help of macro want to store it in a Variable so that i can use this variable in text box for calc. irr by using their id or variable name. like - =xirr(vTOTAL,vTOTAL2)
it should loop through all the values in chart but with specific columns only
Sub ReadStraightTable
total = 0
Set Table = ActiveDocument.GetSheetObject( "CH05" )
For RowIter = 1 to table.GetRowCount-1
For ColIter = 2 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
total = total + cell.Text
'MsgBox(cell.Text)
Next
set v = ActiveDocument.Variables("vTOTAL")
v.SetContent total , True
Next
End Sub
Skid you should not crossposting
making my question more clearer, thats y i reposted a part of my question. but thanks clever.i appreciate ur help.
but wen i ru ur code it shows the error Type mismatch: '[string: "11/1/2016"]'
Does your table have dates too??