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
[Table="width:, class:head"][tr=bgcolor:skyblue][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

1​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

2​

[/td][td]

7​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

3​

[/td][td][/td][td][/td][td]C3Value[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Input[/td][/tr][/table]

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

Using Excel 2007
[Table="width:, class:head"][tr=bgcolor:skyblue][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

2​

[/td][td][/td][td]C3Value[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Output[/td][/tr][/table]

_ 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
[Table="width:, class:head"][tr=bgcolor:skyblue][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

2​

[/td][td][/td][td]=Input!C3[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Output[/td][/tr][/table]
( 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

 

[DocAElstein]

Hi,
This is just a quick edit to a typo someone pointed out to me in my last bit

.........
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 )

It should read:



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. ( But with the Letter/Row Number convention changes to the R Number/C Number convention for cell references. )
Then either:

Pasted directly out to the Sheet ( As .value or .Formula – makes no difference )
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 cell by cell.
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 Variant initially ( Even if only one element in that Array to be outputted is a Formula )
( The above points become clearer when working through the code step by step )