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 icon 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