Convert Array Formula to Values

[gsi07924]

The following piece of code works. However, before performing the loop, I would like to select the range where I am placing the array formula and paste is as values instead of leaving the formula in the range.

Sub Macro1()
Range("A1").Select
   Do
        Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(720, 0)).FormulaArray _
 = "=QLink|Bars!'" & ActiveCell.Value & ,60,720,C,FILL'"                
        ActiveCell.Offset(0, 1).Select
        Loop Until IsEmpty(ActiveCell)
End Sub

Entering code using copy and paste special returns #N/A error as does this

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(720, 0)).Value _
 = Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(720, 0)).Value

Begging your pardon but I am a very novice code writer. I appreciate any assistance.

 

[Richard Schollar]

Hi & Welcome to the Board!

What's the actual formula you are using? Write it exactly as it appears in the worksheet cells. Are you sure the first code you posted is functional (are there typos in the code as it appears in your post)? I don't think in its present state it will compile.

 

[gsi07924]

Here is the array formula. It is a DDE application.

"=QLink|Bars!'" & ActiveCell.Value & ,60,720,C,FILL'"

It does compile. I put underscores in two places so it would fit in one screen. If this is against convention, please forgive me.

 

[Richard Schollar]

Are you sure that that line isn't:

"=QLink|Bars!'" & ActiveCell.Value & ",60,720,C,FILL'"

Note the red double quote. I have zero experience with DDE links I'm afraid - I would have thought doing a copy+pastespecial>values would fix the values or indeed using the Range.Value = Range.Value method (which you quoted in your first post). I am not sure why these wouldn't be working. What happens when you try them?

 

[gsi07924]

Aye, your correction is necessary. Sorry about that.

The DDE links work when entered in my code, but I am using a lot of them so I wanted to convert them into values. When I use the above code or a copy paste special values command, instead of the values, I get an array full of #N/A errors. I even tried a Wait command during the two operations. No such luck.

 

[Richard Schollar]

Maybe try turning off Calculation before you do the copy+pastespecial values?? I'm dubious that will make any difference but it just seems odd that the values change on copying.

Can you successfully copy+pastespecial>values if you do it manually (not using code)?

 

[gsi07924]

Yes, I can manually copy and paste the values without incident. I will try switching off manual calculation.

 

[gsi07924]

Unfortunately, manual calculation doesn't work either.