3

I have a formula in cell "D1" for receiving RTD, which works and get continuously updated data. Now, I need a copy of this cell (only VALUE) in another cell "B2", same worksheet. When i try the code I have found so far, the "B2" cell stays empty. For test reasons I have copied another cell value (constant value) to "B2" and this worked. Therefore, I believe there is a problem with copying RTD values.

I have started in the beginning with simply copying the values:

Sheets("New sheet").Range("B2") = Sheets("New sheet").Range("D1").value

or:

Sheets("New sheet").Range("B2").value = Sheets("New sheet").Range("D1").value

Then I have started to look for other solutions, like:

Sheets("New sheet").Range("D1").Select
enter code here`Selection.Copy
Sheets("New sheet").Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

or:

Sheets("New sheet").Range("B2").Copy Sheets("New sheet").Range("D1").value

Did not have success with this.

Then I played around with the interval the data in "D1" is received. Perhaps the copy code struggles with too frequent data. So i changed the Trottle Interval to several numbers up to 10.000, but this didn't change the result.

Application.RTD.ThrottleInterval=0

Additional information: I enter the formula for receiving RTD data in "D1" directly before I copy the value to "B2". Also tried the application.Wait function to give the data time to update, but since the macro freezes in this time, it made no change. Moreover, have simulated a time delay with a while loop between entering RTD-formula and copy the values to the other cell.

Macro security settings: Enabled trusting VBA-projectObjectModul and all macros are active.

Do you have any ideas how to solve this? Thanks in advance!

UPDATE: I solved it by only placing the RTD formula in "D1" in the current sub and when this sub is finished, I call another sub that copies the value from "D1" to "B2" > This works. It did not work by calling another sub in the current sub, it has to be finished first.

Thank you all for the input, appreciate! :)

Fabian S.
  • 31
  • 3
  • Would [this](https://stackoverflow.com/questions/29116721/rtd-in-excel-vba-need-to-call-data-from-rtd) help you? – Damian Mar 15 '19 at 11:04
  • I have just checked and `Sheets("New sheet").Range("B2").value = Sheets("New sheet").Range("D1").value` this should work. I duplicated this in a worksheet on my side using RTD too and it works. Possible questions to ask: a) What happens when you manually copy and paste? b) Are the cells locked for editing? c) What happens when you recreate this in a new workbook, perhaps you wb is corrupted somehow.. – Dean Mar 15 '19 at 11:10
  • @Damian I looked into it but i believe this is a different problem. – Fabian S. Mar 15 '19 at 11:14
  • @Dean It works if i do it manually with mouse. Actually the second code snippet in my post is the code of the recorded macro to copy the values. – Fabian S. Mar 15 '19 at 11:15
  • @FabianS. if your approach doesn't work, you can always do as they say on that post, get the current value on vba, store it into a variable, or just print it on the cell you want. – Damian Mar 15 '19 at 11:21
  • Do `DoEvents` and/or `Sheets("New sheet").Range("D1").Calculate` help? – Chronocidal Mar 15 '19 at 11:51
  • @Chronocidal Not completely sure where you would place these lines. Just tryed both seperately between the RTD command and getting the value of "D1". Did not work unfortunately – Fabian S. Mar 15 '19 at 11:57

0 Answers0