Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to store table read by macro in a variable??

i have a table rows and columns read by macro code -

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 have to store it in a a variable?? any clue or help needed.

17 Replies
Not applicable
Author

based on dates selection my table ch05 gets updated , ok

now im not able to get XIRR(Payments,ActivityDate) , when i make any change in date (list box - which is in left top, see my attachment). its very challenging question that im trying to solve.

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

That Date field in the list box on the left is totally unconnected to the rest of the model. So using it in CH05 is totally just generating completely bogus numbers. It's all nonsense afaict.


talk is cheap, supply exceeds demand
Not applicable
Author

if you dont want to help dats fine but do not tell like its nonsense, ok if u dont know dats fine too. but u have no right to say anything u want. ok that date list box is connected to CH05 table, ok based on selection of date , the ch05 table changes and i want to find xirr of the dynamic date and payment values.k

Not applicable
Author

and the data is not real, its a dummy , just to test functionality, nobody uploads any original data of the firm or watever it is k

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

nobody uploads any original data of the firm or watever it is k

You'd be suprised. But that doesn't matter. Dummy data is fine as long as it's representative and logical.


talk is cheap, supply exceeds demand
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

The example you posted just doesn't make sense. You simply get a cartesian product when you use that unconnected Date field. Expression results will repeat for each Date value. Why do you need that?

Perhaps you should simple export your straight table directly to excel and calculate whatever you think you need in excel.


talk is cheap, supply exceeds demand
Not applicable
Author

hello gysbert, the example i posted is not correct.

Can you help me or give a suggestion for how to read table (which is having two coulmns, one is date and another one is amount, and it is dyanmic, means the values keeps on changing based on user selection) and calculate XIRR from the two columns of table. if i update the table , then new xirr should be calculated and displayed.

thanks

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Can you post a file (excel is file) with example data and the results you expect based on that data?


talk is cheap, supply exceeds demand