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
_ The current Output Sheet has the value Outputted from Input Sheet
Using Excel 2007
_ This would be the code to achieve this:
_ But the final output I want is this:
Using Excel 2007
( 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
_ 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:
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
Row\Col | A | B | C |
---|---|---|---|
1 | |||
2 | =Input!C3 | ||
3 |
Output |
_ 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