CODEWITHSUNDEEP
arraysexcelvba
sean whelan
sean whelan

Reputation: 37

Ho do I list values of an array in a column based on values from other arrays - Excel VBA

I have 3 arrays, call them Array 1, 2 and 3 (as shown in respective order below).

Array 1 Array 2 Array 3

I'm trying to list the values of Array 2 in a column based on the values in arrays 1 and 3. The column will show the value from array 2 in ascending order from 1 to 12 (based on corresponding value from Array 3), however, if the corresponding value in Array 1 = 1 then the output in the column will be F"n" where n is the corresponding number form Array 3. So, the desired output column is shown below (with corresponding array 3 values shown adjacent for reference):

Output Column

I've been trying to list the values using a loop, but can't seem to resize the array to be a column with the correct values. Any input would be greatly appreciated.

Upvotes: 0

Views: 78

Answers (1)

Sacru2red
Sacru2red

Reputation: 64

can you code VBA ?

Private Sub calcArr()
    On Error Resume Next
    Dim mySheet As Worksheet
    Dim arrayOne As Variant
    Dim arrayTwo As Variant
    Dim arrayThree As Variant
    Dim resultVariant As Variant
    Dim orderNum As Integer
    Dim i As Long
    Dim j As Long
    Dim inputIndex As Long
    
    Set mySheet = ThisWorkbook.Worksheets("Sheet1")
    arrayOne = mySheet.Range("A1:B7")
    arrayTwo = mySheet.Range("C1:D7")
    arrayThree = mySheet.Range("E1:F7")
    ReDim resultVariant(1 To 12, 1 To 2)
    
    For i = 1 To 6
        For j = 1 To 2
            orderNum = VBA.Val(arrayThree(i, j))
            If orderNum <> 0 Then
                inputIndex = inputIndex + 1
                resultVariant(orderNum, 1) = orderNum
                resultVariant(orderNum, 2) = arrayTwo(i, j)
                If arrayOne(i, j) = 1 Then
                    resultVariant(orderNum, 2) = "F" & orderNum
                End If
            End If
        Next j
    Next i
    
    mySheet.Range("A8:B19") = resultVariant


End Sub

Upvotes: 1

Related Questions