Compare few columns from one array with the other, if match change values

[astrbac]

Hi

I have a challenge which is quite a step up from what I was doing in VBA so far. I need to:

• pull two sheets from Excel into arrays
• compare some columns from Arr1 with Arr2
• if found, transpose 12 values from row in Arr1 into column of Arr2

This is what I got so far but I am getting a "Type mismatch" error.
(I'm thinking this is because Application.Index() is returning different value types?)

Private arrPlan() As Variant
Private lastRowSource As Long
Private lastColSource As Long

Private arrRawData() As Variant
Private lastRowDestination As Long
Private lastColDestination As Long


[B]Public Sub Plan_Into_RawData()[/B]

'------------------------ Read Excel ranges into Arrays -----------------
    
    lastRowSource = Sheet1.Range("A" & Rows.count).End(xlUp).Row
    lastColSource = Sheet1.Range("A1").End(xlToRight).Column
    arrPlan = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(lastRowSource, lastColSource))

    lastColDestination = Sheet2.Range("A1").End(xlToRight).Column
    lastRowDestination = Sheet2.Range("A" & Rows.count).End(xlUp).Row
    arrRawData = Sheet2.Range(Sheet2.Cells(1, 1), Sheet2.Cells(lastRowDestination, lastColDestination))


'------------------------ Compare arrays, write amounts ---------------

For i = LBound(arrPlan, 1) + 1 To UBound(arrPlan, 1)
    For j = LBound(arrRawData, 1) + 1 To UBound(arrRawData, 1)
        
        If Application.WorksheetFunction.Index(arrPlan, i, Array(1, 2, 3, 4, 5)) = _
            Application.WorksheetFunction.Index(arrRawData, j, Array(1, 6, 7, 8, 10)) Then
            
                arrRawData(j, 12) = arrPlan(i, 6)
                arrRawData(j + 1, 12) = arrPlan(i, 7)
                arrRawData(j + 2, 12) = arrPlan(i, 8)
                arrRawData(j + 3, 12) = arrPlan(i, 9)
                arrRawData(j + 4, 12) = arrPlan(i, 10)
                arrRawData(j + 5, 12) = arrPlan(i, 11)
                arrRawData(j + 6, 12) = arrPlan(i, 12)
                arrRawData(j + 7, 12) = arrPlan(i, 13)
                arrRawData(j + 8, 12) = arrPlan(i, 14)
                arrRawData(j + 9, 12) = arrPlan(i, 15)
                arrRawData(j + 10, 12) = arrPlan(i, 16)
                arrRawData(j + 11, 12) = arrPlan(i, 17)
            GoTo 10
        
        End If
    Next j
10 Next i

End Sub

 

[GTO]

Hi astrbac,

Unfortunately, today is my Friday/last work day, so I probably won't be back until Sunday night. No doubt you will have received other (and probably better) help by then, but in the meantime...

What is "Array(1, 2, 3, 4, 5)" doing in: .Index(arrPlan, i, Array(1, 2, 3, 4, 5)) ?

I am probably missing something, but the third arg is column: index(array,row_num,[column_num])

Anyways, in case you are wanting to grab one element of the array for the column argument, it would need to be like: Array(1, 2, 3, 4, 5)(n) , where n represents an Integer.

Hope that helps a tiny bit at least.

Mark

 

[astrbac]

Hello Mark, thank you for your reply and effort!

Actually, this bit .Index(arrPlan, i, Array(1, 2, 3, 4, 5)) means that I am extracting elements from 1 row but 5 different columns! I think in this case image speaks a million words so here it goes .

I need to:

> find values from a single row, first 5 columns from the bottom array
> within a single row but first 10 columns of the top array
> when a first match is found, I need to write orange values (for months of the year) from bottom array row, to top array top

Basically, I am disassembling a "manual" pivot table and creating a "database"-like sheet, from which I create many "views" (pivot tables). I hope this explains a bit better

 

[GTO]

Hello again astrbac,

Okay, I do not think I ever used more than one column in Index() and did not realize that you could, so thank you for that.

Given that, it looks to me that you are asking if one array is equal to another. If there's a straight forward way of doing that, I do not know it. As far as I know, you need to compare element to element and asking if arrayA = arrayB is where your type mismatch is occurring.

By example, in a new blank workbook...

Just to add some values:

Sub Setup()
  With Sheet1.Range("A1:J10")
    .Formula = "=""R""&ROW()&""C""&COLUMN()"
    .Value = .Value
  End With
End Sub

To test:

Option Explicit
'
Sub test()
Dim a, b, fBool As Boolean
'
  a = Application.Index(Sheet1.Range("A1:J10").Value, 4, Array(5, 6))
  b = Application.Index(Sheet1.Range("A1:J10").Value, 4, Array(3, 6))
'
  On Error Resume Next
  fBool = (a = b)
'
  If Not Err = 0 Then
    MsgBox Err.Description
  End If
  On Error GoTo 0
'
'
Dim Index As Long, IndexA As Long, IndexB As Long, i As Long, j As Long
'
'
  If (UBound(a) - LBound(a)) = (UBound(b) - LBound(b)) Then
    IndexA = LBound(a) - 1
    IndexB = LBound(b) - 1
'
    For Index = LBound(a) To UBound(a)
      IndexA = IndexA + 1
      IndexB = IndexB + 1
      Debug.Print a(IndexA) & " = " & b(IndexB) & " = " & (a(IndexA) = b(IndexB))
    Next
'
  End If
End Sub

You will see that attempting to check equality of a vs. b results in an error, but you can check the elements.

If you have the immediate window displayed, it will show:

R4C5 = R4C3 = False
R4C6 = R4C6 = True


Hope that helps a little as I was a bit lost in the remaining description. A bit under the weather and cloudy-headed at the moment...

Mark