how to use excel VBA to setup multiple cells with RTD

I have a RTD function that I have been using for years and working fine, now I wonder if there is easy way to create VBA function to set lots of cells with RTD (so I don't have to do copy/paste lots of rows), I created this macro

Please Login or Register  to view this content.

When I run it, it indeed works and call ConnectData for each cell to RTD, but then all of them immediately call DisconnectData (I guess when macro end, it clean them up?)
How/What should I do to make them stay there? If I set up each cell on first row like =mypos(subject, "sn"), ... then copy/paste for multiple rows, they will stay there

Any hint/help?


after some try/fail, I got this work, need to set to formula of the cells and that's the key

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."