Output cell value (not formula) from VBA to Excel

[Formula11]

I have arrays that I want to write from VBA to Excel. The Microsoft website gives advice and gives code similar to what's shown below (http://support.microsoft.com/default.aspx?scid=kb;en-us;306022).

My question relates to the following line:

DataArray(r, 11) = "=RC[+14]"

What this does is input a formula in column 11 from the same row but 14 columns to the right. * The cell contents in Excel have a formula in there, but what I need is a value. *

Any assistance would be appreciated.



Sub Update()
Dim DataArray(1 To 1000, 1 To 20) As Variant
Dim r As Integer
For r = 1 To 1000
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
DataArray(r, 11) = "=RC[+14]"
Next
Sheets("Sheet1").Range("A1").Resize(1000, 20).Value = DataArray
End Sub

 

[John Davis]

I have arrays that I want to write from VBA to Excel. The Microsoft website gives advice and gives code similar to what's shown below (http://support.microsoft.com/default.aspx?scid=kb;en-us;306022).

My question relates to the following line:

DataArray(r, 11) = "=RC[+14]"

What this does is input a formula in column 11 from the same row but 14 columns to the right. * The cell contents in Excel have a formula in there, but what I need is a value. *

Any assistance would be appreciated.



Sub Update()
Dim DataArray(1 To 1000, 1 To 20) As Variant
Dim r As Integer
For r = 1 To 1000
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
DataArray(r, 11) = "=RC[+14]"
Next
Sheets("Sheet1").Range("A1").Resize(1000, 20).Value = DataArray
End Sub

Definitely this is just a stab at it, try:

Replacing "=RC[+14]"

With Cells(ActiveCell.Row, ActiveCell.Column + 14).Value

 

[Formula11]

John, thanks for responding.

Unfortunately that command didn't work, what it does it erase all entries.

 

[Peter_SSs]

The requirement is not entirely clear to me. Do you just want the value from column Y in column K?

If so, try this alternate code in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Update()<br>    <SPAN style="color:#00007F">Dim</SPAN> DataArray(1 <SPAN style="color:#00007F">To</SPAN> 1000, 1 <SPAN style="color:#00007F">To</SPAN> 20) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> r = 1 <SPAN style="color:#00007F">To</SPAN> 1000<br>        DataArray(r, 1) = "ORD" & Format(r, "0000")<br>        DataArray(r, 2) = Rnd() * 1000<br>        DataArray(r, 3) = DataArray(r, 2) * 0.7<br>        <SPAN style="color:#007F00">'DataArray(r, 11) = "=RC[+14]"</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#007F00">'Sheets("Sheet1").Range("A1").Resize(1000, 20).Value = DataArray</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>            .Range("A1").Resize(1000, 20).Value = DataArray<br>            .Range("K1:K1000").Value = .Range("Y1:Y1000").Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>


If this is not what you want please try to clarify further.

 

[Formula11]

Peter, I tried your code and it seems to work fine. Thanks.