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

astrbac

Board Regular
Joined
Jan 22, 2015
Messages
55
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?)

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

Board Regular
Joined
Jan 22, 2015
Messages
55
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 :)

IMG_5022.jpg
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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:
Rich (BB code):
Sub Setup()
  With Sheet1.Range("A1:J10")
    .Formula = "=""R""&ROW()&""C""&COLUMN()"
    .Value = .Value
  End With
End Sub

To test:

Rich (BB code):
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,431
Messages
5,624,749
Members
416,046
Latest member
Elliottj2121

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top