xlf QandA series
How to link an array to a vector source
I WANT TO CONVERT A ROW VECTOR TO A MULTI-ROW ARRAY.? HOW CAN I USE VBA TO WRITE CELL FORMULAE THAT LINK THE ARRAY TO THE SOURCE DATA?
How to link an array to a vector source is shown in the code 1 procedure named LinkArrayToRowVector. The row vector for the
source data is in row 1 of the worksheet named Sheet2, and the first value of the vector is in cell A1. The top left cell of the
target array is cell A1 (cells(1,1)) on the worksheet named Sheet2. The number of columns in the target is assigned to the
ColT variable as shown in line 11 of code 1.
Based on the current data set ( a 1 x 24 vector), the
wsS.Range("A1").CurrentRegion.Columns.Count statement returns the value 24. This value is assigned to the
InLength variable (line 12). This means there are
InLength / ColT (24 / 4 = 6) rows in the target. The formulae are written to the target row by row. The \(i\) index tracks the rows (1 to 6), the \(j\) index tracks the columns (1 to 4), and the \(counter\) variable tracks the elements in the 1 x 24 array (1 to 24).
The For ... Next construct in nested as a double loop in the row and column counters. The 24 formulae are constructed in line 16:
wsT.Cells(i, j).FormulaR1C1 = "=" & wsS.Name & "!r1" & "c" & counter. For example, the first cell is
Sheet2 & !r1 & c & 1 = Sheet2!r1c1. Because the application is set to A1 notation, the formula displays as
Code 1: Construct an array linked to a row vector
Sub LinkArrayToRowVector() '' Convert a row vector to array '' by constructing cell formulae Dim wsS As Worksheet Dim wsT As Worksheet Dim InLength As Long Dim i As Long, j As Long, counter As Long Dim colT As Long Set wsS = Worksheets("Sheet2") '' Source (with data in row 1) Set wsT = Worksheets("Sheet1") '' Target colT = 4 '' Number of columns in target InLength = wsS.Range("A1").CurrentRegion.Columns.Count For i = 1 To InLength / colT For j = 1 To colT counter = counter + 1 wsT.Cells(i, j).FormulaR1C1 = "=" & wsS.Name & "!r1" & "c" & counter Next j Next i End Sub
- This example was developed in Excel 2013 64 bit
- Published: 23 June 2014
- Revised: Friday 22nd of June 2018 - 09:52 PM, [Australian Eastern Standard Time (EST)]