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]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[/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]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[/table][Table="width:, class:grid"][tr][td]Output[/td][/tr][/table]
_ This would be the code to achieve this:
_ But the final output I want is this:
Using Excel 2007
[Table="width:, class:head"][tr=bgcolor:skyblue][th]Row\Col[/th][th]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
[/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
_ 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