xlf QandA series
How to link an array to a vector source
QUESTION
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?
Answer
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 =Sheet2!A1
.
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: Saturday 25th of February 2023 - 09:39 AM, [Australian Eastern Standard Time (EST)]