calculate irr in table box

[#1]

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.

[#2]

the link is QlikView Maven: Macro Copies Value From a Chart into a Variable

[tamilarasu]

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 .

[Clever_Anjos]

If you really want to calculate IRR using a macro, maybe you can mix that code you have with this

http://www.mrexcel.com/forum/excel-questions/410018-writing-irr-function-visual-basic-applications.h...

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

[#5]

the values would store like -100

                                          200

                                         -100

like this

[#6]

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)

[#7]

it should loop through all the values in chart but with specific columns only

[Clever_Anjos]

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

[Clever_Anjos]

Skid you should not crossposting

macro to copy row and column from chart to variable

[#10]

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"]'

[Clever_Anjos]

Does your table have dates too??