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

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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:

Rich (BB code):
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

Rich (BB code):
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
 
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 )
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top