Formula for Link to a Cell from Array instead of that cell value based on Cell r c co-ordinates

[DocAElstein]

Hi
_ I am sure this can be done but am struggling with the syntax. ( I have no experience with using VBA to paste out formulas to cells, or using VBA to store those formulas within an Array for later pasting out to the sheet )

_ Currently I build an Array of values to finally be outputted to an output sheet ( That is based on various looping with checking matching criteria in a selection of cells from an Input Sheet ( or rather an Input Array thereof ) etc. Etc..... But not too relevant to my current problem )
_ A typical Code line to put the selected Value from the Input Sheet Input Array into the Output Array would be of the form arrOut(2, x ) =arrIn(r , 3)
¬_
_ The basic code type must stay the same. But I require instead the link to the Cell rather than the Cell value. Please no alternative code based on Copy Paste, etc.

_ As a simple demonstration
The Input Sheet situation:

Using Excel 2007

Row\Col	A​	B​	C​	D​
1​	6​
2​	7​
3​			C3Value
4​

Input

_ The current Output Sheet has the value Outputted from Input Sheet

Using Excel 2007

Row\Col	A​	B​	C​
1​
2​		C3Value
3​

Output

_ This would be the code to achieve this:

Option Explicit
Sub CellValuelinkInArray()
Dim wsIn As Worksheet, wsOut As Worksheet
Set wsIn = ThisWorkbook.Worksheets("Input"): Set wsOut = ThisWorkbook.Worksheets("Output")

Dim arrIn() As Variant
Let arrIn() = wsIn.UsedRange.Value
Dim arrOut() As Variant: ReDim arrOut(1 To 6, 1 To 6)

Dim r As Long: Let r = 3 'The r value would be found by some criteria looping search normally
Dim x As Long: Let x = 2
'Input some arbritrary "row" Value from Input Array into Output Array,
Let arrOut(2, x) = arrIn(r, 3)

'Input the link to the Input sheet Cell(r,3) into Output Array
'HELP!___  Required is somehow here arrOut(2, x) =Input!C3 such that Final output is Link =Input!C3

'Output arrOut() to output Sheet
Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()

End Sub

_ But the final output I want is this:

Using Excel 2007

Row\Col	A​	B​	C​
1​
2​		=Input!C3
3​

Output

( Note: I am showing in the screen shot above the Formula that I want in the cell, as seen in the formula bar. - In the sheet of course i still want to see the evaluated “C3Value“ )

_ Can anyone please modify my code to do this. Bearing in mind the “row” given by r is a variable
( In the code above I want the HELP! ___ Code line. The basic code type and layout must stay the same )

_ Thanks
Alan

P.s. 1 If it helps: This formula would get the column letter from the column Number

Sub LetterFromColumnNumber()
Dim ColumnLetter As String, c As Long

Let c = 3 'Arbritrary Column number
Let ColumnLetter = Cells(1, c).Address 'Gives $C$3
Let ColumnLetter = Replace(ColumnLetter, "$", "", 1, 1) 'Gives C$3
Let ColumnLetter = Mid(ColumnLetter, 1, (InStr(ColumnLetter, "$") - 1)) 'Gives C
'Or Finally
Let ColumnLetter = Mid(Replace(Cells(1, c).Address, "$", "", 1, 1), 1, (InStr(Replace(Cells(1, c).Address, "$", "", 1, 1), "$") - 1)) 'Gives C
End Sub

 

[Comfy]

Hi Alan,

Have a look at R1C1 notation: https://msdn.microsoft.com/en-us/library/office/ff823188.aspx

The VBA for your example above would be.

Cells(2,2).FormulaR1C1 = "=Input!R3C3" 'Row 3, Column 3 being "C".  This would be absolute. $C$3
Cells(2,2).FormulaR1C1 = "=Input!R[1]C[1]" 'One more row than the current & One more column than the current.  This would be relative. C3

No need to mess around with number to letter conversions.

 

[DocAElstein]

Hi Comfy

....
Have a look at R1C1 notation:......
The VBA for your example above would be.
.......

_ Thanks comfy for the quick reply and helpful Info. Appreciate it. The explaining comments after the code lines was very helpful.
_
_ Unfortunately I am still no further with my actual requirement.
_ Maybe I did not explain clearly enough or I have misunderstood your answer
_
_ I think you have shown me how to paste a formula to a sheet when i know the row and column numbers as numbers.
_
_ My situation is this:

_1 ) My row number will in my final code, ( and as in my example code in Post #1) be contained in a Variable , r

_ 2) I would like that Formula to be put into an output Array so that when at the end of the code the Array is outputted to the Output sheet the formula is outputted ( Along with everything else that may be in the Array )

_ Can you help me there? It is the line which I wrote as
‘HELP!___
Which I need to achieve this
This is my “pseudo” Code to help demonstrate

'Input the link to the Input sheet Cell(r,3) into Output Array ( r is long variable containing the row number)
Let arrOut(2, x) = Formula("=Input!" & r & "3C3")

Or even better to use the defined Input worksheet Object, “pseudo” code again

'Input the link to the Input sheet Cell(r,3) into Output Array ( r is long variable containing the row number )
Let arrOut(2, x) = Formula("=" & wsIn.Name & "!" & r & "3C3")

Thanks again
Alan

 

[Comfy]

Your arrOut would contain the formula as a string.
And when writing that array to the output worksheet you should use .FormulaR1C1

Option Explicit
Sub CellValuelinkInArray()
Dim wsIn As Worksheet, wsOut As Worksheet
Set wsIn = ThisWorkbook.Worksheets("Input"): Set wsOut = ThisWorkbook.Worksheets("Output")


Dim arrIn() As Variant
Let arrIn() = wsIn.UsedRange.Value
Dim arrOut() As Variant: ReDim arrOut(1 To 6, 1 To 6)


Dim r As Long: Let r = 3 'The r value would be found by some criteria looping search normally
Dim x As Long: Let x = 2
'Input some arbritrary "row" Value from Input Array into Output Array,
Let arrOut(2, x) = "=Input!R" & r & "C3" 'arrIn(r, 3)


'Input the link to the Input sheet Cell(r,3) into Output Array
'HELP!___  Required is somehow here arrOut(2, x) =Input!C3 such that Final output is Link =Input!C3


'Output arrOut() to output Sheet
Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).FormulaR1C1 = arrOut()


End Sub

The problem arises if you are trying to output values to some cells and formulas to other.

 

[DocAElstein]

Hi Comfy
_ Thanks again for the continued help

Your arrOut would contain the formula as a string.

_ In parallel i had experimented with my “pseudo” codes and got the following 3 code lines

Let arrOut(2, x) = "=Input!R3C3" 'Works
Let arrOut(2, x) = "=Input!R" & r & "C3" 'Works
Let arrOut(2, x) = "=" & wsIn.Name & "!R" & r & "C3" 'Works

_ to work. One of the lines matches yours - so that is encouraging!
_
_ ( If I am finally getting to understand a bit about how VBA is working / Thinking then, me writing
= “_______________”
Is telling VBA that what is coming is a string. Correct ? ( I mean the first “ say - here comes a string -and then second says " - that’s the end of it -) )
_ 1 ) Correct ?

...............................................

....
And when writing that array to the output worksheet you should use .FormulaR1C1..........................
The problem arises if you are trying to output values to some cells and formulas to other.

I found it still worked fine using .Value rather than your suggested .FormulaR1C1

. So I experimented further. This time my Input sheet looked like this
Using Excel 2007

Row\Col	A​	B​	C​	D​
1​	6​
2​	7​
3​	A String		C3Value
4​

Input

_ and I added two lines in the original code to take in a number and A string from the Input sheet. And
I tried both .Value and .FormulaR1C1 in the last lines for Output of arrOut() to the Output Sheet

Sub CellValuelinkInArrayComfyAlan()
Dim wsIn As Worksheet, wsOut As Worksheet
Set wsIn = ThisWorkbook.Worksheets("Input"): Set wsOut = ThisWorkbook.Worksheets("Output")

Dim arrIn() As Variant
Let arrIn() = wsIn.UsedRange.Value
Dim arrOut() As Variant: ReDim arrOut(1 To 6, 1 To 6)

Dim r As Long: Let r = 3 'The r value would be found by some criteria looping search normally
Dim x As Long: Let x = 2
'Input some arbritrary "row" Value from Input Array into Output Array and a Formula for a Link,
Let arrOut(1, x) = arrIn(1, 1)
Let arrOut(2, x) = "=Input!R3C3" 'Works
Let arrOut(2, x) = "=Input!R" & r & "C3" 'Works
Let arrOut(2, x) = "=" & wsIn.Name & "!R" & r & "C3" 'Works
Let arrOut(3, x) = arrIn(3, 1)
'Output arrOut() to output Sheet
Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).FormulaR1C1 = arrOut()
End Sub

__
Both .Value and .FormulaR1C1 work to give me my desired output.
_ BUT , I very much value your experience..

................
The problem arises if you are trying to output values to some cells and formulas to other.

_ Maybe I have been lucky with this limited example. I would very much appreciate it if you could advise what problem you think I could run in to..
_ ( I wonder if my Dim of arrOut() as Variant ( By choice ) along with my Dim arrIn() as variant ( necessary to meet the returned Variant Field collection from the .Value “one liner” assignment the Input sheet Used Range ) has taken care of any problems there.
_ 2) Correct ?
Or
_ 3) Is it just that I actually assign the .Value or .Formula to the spreadsheet Range, but that VBA ( or rather the Excel Spreadsheet ) “guesses” right and for example for .Value assignment takes the string =“____” as a formula rather than as Text, and that the other way around if something is given as a .Formula but has no = bit then it will default to VBA putting it in ( or spreadsheet taking it ) as a value.
_ 4 ) Would the problems likely to come if I change my spreadsheet Cell format type to something other than the default standard “

_ 5) Or can you think of any other issues. ?
_
_ Thanks again very much for sharing your knowledge here
Alan

 

[Comfy]

Both .Value and .FormulaR1C1 work to give me my desired output.

Well that's good, it means my foreseen problem isn't a problem.

= “_______________”
Is telling VBA that what is coming is a string. Correct ? ( I mean the first “ say - here comes a string -and then second says " - that’s the end of it -) )

Correct. All of these are strings ("Excel" "Mr Excel Forum" "300")

_ ( I wonder if my Dim of arrOut() as Variant ( By choice ) along with my Dim arrIn() as variant ( necessary to meet the returned Variant Field collection from the .Value “one liner” assignment the Input sheet Used Range ) has taken care of any problems there.

Correct. Variant can be any Data Type https://msdn.microsoft.com/en-us/library/office/gg251448.aspx

_ 3) Is it just that I actually assign the .Value or .Formula to the spreadsheet Range, but that VBA ( or rather the Excel Spreadsheet ) “guesses” right and for example for .Value assignment takes the string =“____” as a formula rather than as Text, and that the other way around if something is given as a .Formula but has no = bit then it will default to VBA putting it in ( or spreadsheet taking it ) as a value.

a) This is partly my fault, I just assumed (without testing) that if you used .Value Excel would enter '=SUM(A1:B1) in the cell so you would see the formula instead of the result.
b) If you used .Formula without the '=' Excel would just put "SUM(A1:B1)" in the cell.

I now know, due to testing it, that using .Value, .Formula, .FormulaR1C1 makes no difference. As long as you start the string with an equals sign Excel will interpret it as a formula.

_ 4 ) Would the problems likely to come if I change my spreadsheet Cell format type to something other than the default standard “

I can't see that a cell format would have any effect on the content.

_ 5) Or can you think of any other issues. ?

Nope, only time and testing would tell.

 

[DocAElstein]

Hi Comfy,
_ Thanks very much for coming back with that very full and detailed answer. I think that has really helped me to get that all clear in my head now.

_ One last point

......I can't see that a cell format would have any effect on the content....

. I just changed the cell format to Text and re-ran my program. I actually get then the following ( I am showing the values in the screen shot ( wot I actually see being displayed, not the formulas )
Using Excel 2007

Row\Col	A​	B​	C​
1​		6​
2​		=Input!R3C3
3​		A String
4​

Output

_............................

I think I expected that. ( you too ? ). - Being German I have horrendous problems with Excel deciding to change comers to points, numbers to dates etc. Etc. Changing to Text Format often helps. It seems to pretty well make excel display exactly what you write ( or in this case paste ) into the cell

Many Thanks again
Alan.

P.s. "300" in your "Excel" "Mr Excel Forum" "300" string examples was good..
_ that reminds me of an experiment I did recently
Special concatenation - Page 3

Dim v As Variant
Let v = "3" ' Results in a Variant variable containing astring value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )
_ that is where I first got the idea that a “ says here comes a string and a second “ says that was the end of it !!

P.P.s

...... only time and testing would tell.

_ I am continually surprised how much I find I have to learn by experimenting, but encouraging at least to find that Profis confirm that it is so.................
_...................................

 

[Comfy]

Happy to Help.

Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

Yep, is an integer. While I have no idea about the decision making, classifying objects as an integer is no longer really relevant due to advances in technology (memory size etc)
It would only have been used if there were memory limitations.

_ I am continually surprised how much I find I have to learn by experimenting

I find it's the only way to learn. You can ready books etc all you want but you need to apply that learning for it to become knowledge.

 

[DocAElstein]

Hi Comfy,
_ Thanks again.
Dim v As Variant
Let v = "3" ' Results in a Variant variable containing astring value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

...Yep, is an integer. While I have no idea about the decision making, classifying objects as an integer is no longer really relevant due to advances in technology (memory size etc).......

. that helped to confirm that for me, and saved a possible follow up question I was thinking of posting here:
http://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop.html#post3933730
_ so it seems VBA is being a bit silly here, as it will generally these days convert back to ( or occasionally it will even convert a non whole number ) to Long ( Long being the biggest whole Number type you have in VBA ) before using it further.
_ It is a very minor , picky point, but the small things I have found in my short VBA time, worth getting straight at the start, or it can catch you out later
_ So I appreciate you coming back for what must seem very trivial stuff for you. It has made it a very helpful and rich Thread.

Alan

.................
. The following is not too important - you may be fed up with this Thread already!!

P.s. Did my Text Format in the Spreadsheet point from Post #7 seem reasonable. Putting it in other words again: “Changing a Spreadsheet Cell Format to Text is something which stops Excel interfering with what you write or with VBA paste into a cell. As far as any text is concerned, the cell reacts like a simple text document file and exactly what you put in there is seen. ( .. well Almost ! - my last experiment code running again but before doing so I have put the Spreadsheet cells in the Output Range to Text format, but with slanted and red colour text...
Using Excel 2007

-	A​	B​	C​
1​		6​
2​		=Input!R3C3
3​		A String
4​

Output

P.P.s.
Just out of passing interest:
As for...

... I am continually surprised how much I find I have to learn by experimenting.....

.........I find it's the only way to learn. ...

_ his an interesting one, did a bit of experimenting in the code below.:
_ 1)
_ starting at my
Dim v As Variant
Let v = 3
_ then increase the number through the size limit of Integer, then further through the size limit of Long...
_ The result is that VBA puts first an Integer type, then a Long type, ...and then at the top Long size limit... it puts a Double Type in ( so misses the Single Stage ! ) but also sticks a # on the end!!?!. I did Google through some Forum Posts giving that # as one of the things to use as an alternative way of dimensioning things, # being the option for Double..
Excel VBA to copy and paste from horizontal to vertical format
_ .................But it is not really a way of doing it, as my experiments then showed you could easily change that with a assignment such as = “ “ to a string...... but another Theme for another Day..
_
_ 2)
_ Any mathematical operation on a Variable of Variant Type with a String Type in it that “looks “ like a number , such as a simple x 1, will turn a variant Type with a String Type in it to a variant with a double in it, even if it is a very small whole number. So behaves a bit differently – going straight to a Double without the in – between Integer then Long Bit

__ So yes, bottom line is experimenting is needed a lot..!!!

Sub StringLongIntegerComfyAlanStuff()
Dim v As Variant           ' v is Variant type and empty
Rem 1) ' Varinat v as a Number
Let v = 3 '                 ' v becomes Variant Type with Integer Type value 3 in it
Let v = 32767: Let v = -32767 ' v becomes Variant Type with Integer Type value 32767 or -32767 in it
Let v = 32768: Let v = 2147483647 ' v becomes Variant Type with Long Type value 32767 or 2147483647 in it
'   Let v = 2147483648  ' This will not work and the followomg line applies
Let v = 2147483648# ' VBA puts the # on and v becomes Variant Type with Double Type value 2147483648 in it

Let v = 3# '    'I put the # on...  v becomes Variant Type with Double Type value 3 in it

Rem 1b) Mysterious alternative to Dimension something
'    'NOTE:  Next two lines will only work with Option Explicit Disabled
'    Let u = 3# '      u becomes Variant Type with Double Type value 3 in it
'    Let u = "3"  'u becomes Variant Type with a string Type value 3 in it

Rem 2)
' Variant v as a String
Let v = "3" '                 ' v becomes Variant Type with String Type value 3 in it
Let v = "2147483648"         ' v becomes Variant Type with String Type value 2147483648 in it
Let v = v * 1                'v becomes Variant Type with Double Type value 2147483648 in it

Let v = "32767" '                 ' v becomes Variant Type with String Type value 32767 in it
Let v = v * 1 '               'v becomes Variant Type with Double Type value 32767 in it

Let v = "3" '                 ' v becomes Variant Type with String Type value 3 in it
Let v = v * 1 '               'v becomes Variant Type with Double Type value 3 in it


End Sub

http://www.excelforum.com/excel-pro...izontal-to-vertical-format-2.html#post4194972

 

[DocAElstein]

How do I store a formula in VBA as a Variable to be pasted out so that it works as if i had typed it in correctly by hand

Hallo,
_ I think this is good follow up and good reference Post for later based on the Thread Title and content so far....
_ maybe the following is documented and many people know the following but I struggled to find it and hit on it by chance through experimenting, so am sharing ..

_ Briefly again the basic idea is / was
_ 1) to build up an Array of formula ( “Strings” ) to be Pasted out to the spreadsheet in one go
_ 2) Range references may be known by sheet Name and the Row and Column Indicies as whole numbers.
_ 2a) At the outset to hopefully avoid confusion my Dimensioned ( as Long ) Numbers for any row will be r or j and any column will be c or i and I hope this does not confuse with R and C which somehow VBA seems to recognise as a Row and Column reference.......

_ I am basically trying to put into simple words how to put a formula into a cell, possibly storing it in the meantime in A Variable or Array element. Sounds simple. What i have found by experimenting I could not clearly in English find spelt out anywhere!!? Maybe I hit on it coincidentally as i have the added confusion of my formulas all looking different than the English versions ( I have German Excel )

_ As simple example some Input sheet captured in a “One Liner”
50 Dim arrIn() As Variant: arrIn() = wsIn.UsedRange.Value
( Complete code here:
Excel Help Forum
.. which i will update and correct )
And again at the end of this Post


Using Excel 2007

-	A​	B​	C​
1​	6​	1​
2​	7​	2​
3​	A String	3​	C3Value

Input

_ An Internal Array arbitrarily dimensioned as 6 x 6
60 Dim arrOut() As Variant: ReDim arrOut(1 To 6, 1 To 6)
will be used to store output values and formulas created internally,

_ A simple manually entered formula in Output sheet to link cell Output Sheet C1 to Input sheet B1 looks like this
=Input!B1 ( In English or German )

If I put that in my Output Array, then I get it in my Output Array !
arrOut(1, 3) = "=Input!B1": Debug.Print arrOut(1, 3) ' Returns =Input!B1 ( held in Array As String )
If my code pastes that out to the Output Sheet VBA “interferers and i get this outputted,
=Input!'B1' which Excel errors ?!?

( In passing, If I manually type into Output Sheet C1 =Input!R1C2 excel errors also as expected, )

The crux of the first few posts in this Thread showed various ways to construct that last expression into
arrOut(1, 3) = "=Input!R1C3": Debug.Print arrOut(1, 3) ' Returns =Input!R1C2 ( held in Array As String )

If my code pastes that out to the Output Sheet VBA “interferers" at the pasting stage and i get this outputted
=Input!$C$1 which Works!

So then I wrote lines 130 to 170 do something similar to bring in numbers 1 2 and 3 from Input Sheet into my Output Sheet

So far, ( almost ) so good, ( Just the weird VBA interfering To give =Input!'B1'
_ .............

Lets take it a bit further for Putting a formula in the output sheet say to sum those brought over values 1 2 and 3 .
Manually typed in by me ( German ) as
=SUMME(C1:C3)

By you ( maybe, if you are English or similar ) typed in as in the complete screen shots
Formulas:
Using Excel 2007

-	C​	D​	E​
1​	=Input!$B$1​	=Input!$B$1​
2​	=Input!$B$2​	=Input!$B$2​
3​	=Input!$B$3​	=Input!$B$3​
4​	=SUM($C$1:$C$3)​	=SUM($D$1:$D$3)​
5​

Output

Values:
Using Excel 2007

-	C​	D​	E​
1​	1​	1​
2​	2​	2​
3​	3​	3​
4​	6​	6​
5​

Output

No problem with manually entered.

However, On pasting out Strings ( seen to be by Debug.Print excactly those formulas ) using my code Line 200 , VBA Interferers Giving me
=SUMME($A:$C) which errors!

Clearly the letter C tells VBA to do something on pasting a string into a cell if it starts with = , which seems fundamentally to be the indication for VBA that a formula is in play, ( not the .Formula , which as discussed in this Thread earlier seems to have little effect.... and reacts the same as .Value )

The above example was unfortunate perhaps in choosing column C as C means something special when VBA “interferres” at the pasting stage.

Repeating the experiment for column D and formula =SUMME(D1:D3), pasting out, VBA “Interferers” Giving me
=SUMME('D1':'D3') which errors also!

So my guess for success was getting VBA to interfere in a “positive way” , that is to say trying...
arrOut(4, 4) = "=SUMME(R1C4:R3C4)” and arrOut(4, 3) = "=SUMME(R1C3:R3C3)"
it almost worked . I get the correct ( German ) Formulas pasted into the cells as
=SUMME($D$1:$D$3) and =SUMME($C$1:$C$3)
Initially they errored. On selecting the Formula bar and hitting enter, they worked. Weird ?!?

A shot in the dark was to use the English Formulas in my code as i know they error in my Excel if i type them in manually . .....
arrOut(4, 4) = "=SUM(R1C4:R3C4)” and arrOut(4, 3) = "=SUM(R1C3:R3C3)"
As before I get exactly that String stored in my Array. So obviously no chance of it working. Or so I thought. Ha! VBA now “interferes” at the pasting stage and not only turns it into the correct ( German ) Language form, but it also works without that last Weird requirement of selecting the Formula bar and hitting Enter. ( So i have invented how to get VBA to put a formula in a cell. useful. )
_...............................................................................

My Point and conclusions here is that in the building of my formulas in VBA:
_ 1 ) the R C within a string plays an important role causing VBA and or Excel to interfere at the Pasting stage with what is actually pasted in, that is to say it is converted to the correct cell reference.
_ 2 ) using an English Formula causes VBA and or Excel to interfere with what is actually pasted in, that is to say give the correct formula for the version of language version of Excel in use
_ 3) maybe something else ? ! ? to do with the strange ‘ which i do not know yet.
_ 4) The first “=” is what seems to start VBA and or Excel to interfere in what is actually pasted in the cell, the relevance of .FormulaR1C1 as opposed to .Value appears lost and Redundant
( _ 5) A last strange bit: In general all the formulas can be stored in Variant type Variables ( as strings ) or in String Type Variables as Strings. Even for the case of Arrays of more than one Element. Then pasting out one formula at a time will work.
But for some strange reason if pasting out a Multidimensional Array to a Spreadsheet range in one go then the Array must be dimensioned as Variant ( see lines 450 – 500 ) For some reason VBA “ interferers” a bit differently in the one Liner Output assignment of a data field of size )

My final conclusion.
Question:
How do I write or store a formula in VBA as a Variable to be pasted out so that it works as if I had typed it in correctly by hand
Answer:
In general:
The Formula must be written exactly as it would be written in the Cell ( including the = at the start ) using the English version of the Formula.
Then either:

Pasted directly out to the Sheet
Or
Stored as a String in a String or Variant Type variable or as a String in a Variant or String type / types Element / Elements of an Array and pasted out line by line.
Or
If intended to store more than one Element In an Array and paste out all values at once in a one Liner assignment then the Array Elements must be Dimensioned as errors initially but on hitting Enter Returns correct working formula Variant ( Even if only one element is a Formula )

I am a novice and i would very much appreciate any Profi Comments ( or insults ) to my conclusions.. I am particularly worried that i may be relying on some strange Implicit ( what i am referring to as VBA or Excel “ interfering” ) which might bite me in the A_s_ sometime later

Many Thanks for watching
Alan



Code:

 Sub LinkAndFormulaInArrayForOutputToRange() '      http://www.mrexcel.com/forum/excel-questions/887822-formula-link-cell-array-instead-cell-value-based-cell-r-c-co-ordinates.html
10  'Some Workshhet Info
20  Dim wsIn As Worksheet, wsOut As Worksheet
30  Set wsIn = ThisWorkbook.Worksheets("Input"): Set wsOut = ThisWorkbook.Worksheets("Output")
40  Dim arrIn() As Variant:
50  Let arrIn() = wsIn.UsedRange.Value
60  Dim arrOut() As Variant: ReDim arrOut(1 To 6, 1 To 6) 'Will not work if Dimensioned as String - see lines 450 - 500
70  wsOut.Range("A1:F6").Clear 'Get rid of everything including any strange formatting
80
90  'Input some arbritrary "row" Value from Input Array into Output Array by pasting in appropriate link.
100 Dim r As Long: 'The r  and x value would be found by some criteria, looping, search, etc. in an actual File
110 Dim x As Long: Let x = 2
120 Let arrOut(1, 3) = "=Input!B1": Debug.Print arrOut(1, 3) '  Returns   =Input!B1   (  held in Array As String  )  Pastes out  =Input!'B1'   which errors
130 Let arrOut(1, 3) = "=Input!R1C2": Debug.Print arrOut(1, 3) '  Returns   =Input!R1C3   (  held in Array As String  )  Pastes out  =Input!$C$1   which Works
140     For r = 1 To 3 Step 1
150     Let arrOut(r, 3) = "=" & wsIn.Name & "!R" & r & "C" & x & "":   Debug.Print arrOut(r, 3)
160     Let arrOut(r, 4) = "=" & wsIn.Name & "!R" & r & "C" & x & "":   Debug.Print arrOut(r, 3)
170     Next r
180 '
190 'Non Working Formulas
200 Let arrOut(4, 3) = "=SUMME(C1:C3)": Debug.Print arrOut(4, 3) '  Returns   =SUMME(C1:C3)   (  held in Array As String  )  Pastes out  =SUMME($A:$C)   which errors
210 Let arrOut(4, 4) = "=SUMME(D1:D3)": Debug.Print arrOut(4, 4) '  Returns   =SUMME(D1:D3)   (  held in Array As String  )  Pastes out  =SUMME('D1':'D3')   which errors
220 Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut() 'Errors
230 Let arrOut(4, 4) = "=SUMME(R1C4:R3C4)": Debug.Print arrOut(4, 4) '  Returns  =SUMME(R1C4:R3C4)   (  held in Array As String  )  Pastes out  =SUMME($D$1:$D$3)   which errors initially but on hitting Enter Returns correct working formula
240 Let arrOut(4, 3) = "=SUMME(R1C3:R3C3)": Debug.Print arrOut(4, 3) '  Returns  =SUMME(R1C3:R3C3)   (  held in Array As String  )  Pastes out  =SUMME($C$1:$C$3)   which errors initially but on hitting Enter Returns correct working formula
250
260 Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut() 'errors initially but on hitting Enter Returns correct working formula
270 Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).FormulaR1C1 = arrOut() 'errors initially but on hitting Enter Returns correct working formula
280      'wsOut.Range("C4").FormulaR1C1 = wsOut.Range("C4").Value: wsOut.Range("D4").Select: wsOut.Range("C4").Select: 'All this does not help
290
300 'Working Formulas Use R and C for cell referrences and English Formulas
310 Let arrOut(4, 4) = "=SUM(R1C4:R3C4)": Debug.Print arrOut(4, 4) '  Returns  =SUM(R1C4:R3C4)   (  held in Array As String  )  Pastes out  =SUMME($D$1:$D$3)   which Works!
320 Let arrOut(4, 3) = "=SUM(R1C3:R3C3)": Debug.Print arrOut(4, 3) '  Returns  =SUM(R1C3:R3C3)   (  held in Array As String  )  Pastes out  =SUMME($C$1:$C$3)   which Works!
330
340
350 Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
360 Let wsOut.Range("a1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).FormulaR1C1 = arrOut()
370
380 'Alternatives: Direct Output or String Variables or Single Element Arrays Variables
390 Let wsOut.Range("D4").Value = "=SUM(R1C4:R3C4)" ' Works
400 Let wsOut.Range("D4").Value = "=SUMME(R1C4:R3C4)" '   errors initially but on hitting Enter Returns correct working formula
410 Dim strFormulaD4 As String: Let strFormulaD4 = "=SUM(R1C4:R3C4)": Let wsOut.Range("D4").Value = strFormulaD4 'Works
420 Let strFormulaD4 = "=SUMME(R1C4:R3C4)": Let wsOut.Range("D4").Value = strFormulaD4 '  errors initially but on hitting Enter Returns correct working formula
430 Dim strLinkD3(0) As String: Let strLinkD3(0) = "=Input!R3C2": Let wsOut.Range("D3").Value = strLinkD3(0) 'Works
440 Dim strArrayFormulaD4(0) As String: Let strArrayFormulaD4(0) = "=SUM(R1C4:R3C4)": Let wsOut.Range("D4").Value = strArrayFormulaD4(0) 'Works
445
447 'Demo to show that For pasting out of more than one Element / Formula at a time , The Array must be Variant Type
450 Dim strarrOut2(1 To 2, 1 To 1) As String
460 Let strarrOut2(1, 1) = "=Input!R3C2": Let strarrOut2(2, 1) = "=SUM(R1C4:R3C4)"
465 Let wsOut.Range("D3").Value = strarrOut2(1, 1) 'Works
467 Let wsOut.Range("D4").Value = strarrOut2(2, 1) 'Works
470 Let wsOut.Range("D3:D4").Value = strarrOut2() 'Does not work. Outputs Text of Link and Formula
473 Let wsOut.Range("D3:D4").Formula = strarrOut2() 'Does not work. Outputs Text of Link and Formula
475 Let wsOut.Range("D3:D4").FormulaR1C1 = strarrOut2() 'Does not work. Outputs Text of Link and Formula
476 Let strarrOut2(1, 1) = 3: Let strarrOut2(2, 1) = "=SUM(R1C4:R3C4)"
478 Let wsOut.Range("D3:D4").FormulaR1C1 = strarrOut2() 'Does not work. Outputs 3 and Text of Formula
479
480 Dim vararrOut2(1 To 2, 1 To 1) As Variant
490 Let vararrOut2(1, 1) = "=Input!R3C2": Let vararrOut2(2, 1) = "=SUM(R1C4:R3C4)"
500 Let wsOut.Range("D3:D4").Value = vararrOut2() 'Works

 End Sub