Index and Match array in VBA

[Lefemmenikita]

Hi

I am dealing with thousands of rows of data.

I am trying to use the multiple criteria index and match in a VBA formula to return a result.

The formula I am currently using is:

Selection.FormulaArray ="=IF(RC[-3]=""Billable"",INDEX(Coding!C1:C5,MATCH(1,(Coding!C1=Data!RC11)*(Coding!C2=Data!RC1),0),3),INDEX(Coding!C1:C5,MATCH(1,(Coding!C1=Data!RC11)*(Coding!C2=Data!RC1),0),4))"
    Range("P2").Select
   
    Columns("P:P").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

This runs out of memory before cycling through all the cells in the column and returning a result

My question is:

Is there a way to make an index/match formula use a dynamic column reference? (therefore, eliminating the need to have an "if" statement in the above array formula)?

Also, is there a way to have an array index/match formula execute via VBA without running out of memory?

I am able to do this outside of VBA, though it takes around 5 mins for Excel to finish calculating the result before I am able to replace the formula with the calculated value.


Hope my question makes sense

Thanks

 

[offthelip]

whoops sorry, I have just noticed that i left out a whole load more dots, trry this version:

Sub TEST()

With Worksheets("Coding")
' I assume:
'AcountDescription in column B
'Acount code in column A
' Billable value in column C
' Nonbillable Value in column D
 
 Lastcode = .Cells(Rows.Count, "A").End(xlUp).Row
 Coder = Range(.Cells(1, 1), .Cells(Lastcode, 4))
End With
With Worksheets("Data")
' I assume:
'AcountDescription in column A
'Acount code in column K
' Billable/Nonbillable  in column M
' the result in column P


 lastdata = .Cells(Rows.Count, "A").End(xlUp).Row
 Datar = Range(.Cells(1, 1), .Cells(lastdata, 13))
 
 Range(.Cells(2, 16), .Cells(Lastcode, 16)) = "Not found"
 Results = Range(.Cells(1, 16), .Cells(Lastcode, 16))


' I assume there is a header row so I start on row 2
For j = 2 To lastdata
  For i = 2 To Lastcode
    If Coder(i, 1) = Datar(j, 11) And Coder(i, 2) = Datar(j, 1) Then
     ' code and description match so check billable
      If Datar(j, 13) = "Billable" Then
       Results(j, 1) = Coder(i, 3)
      Else
       Results(j, 1) = Coder(i, 4)
      End If
     Exit For
    End If
   Next i
 Next j
' write the results out
 Range(.Cells(1, 16), .Cells(Lastcode, 16)) = Results
End With
 
End Sub

 

[Lefemmenikita]

Hi. Just tested it now and am getting a subscript out of range around:

' I assume there is a header row so I start on row 2For j = 2 To lastdata
  For i = 2 To Lastcode
    If Coder(i, 1) = Datar(j, 11) And Coder(i, 2) = Datar(j, 1) Then
     ' code and description match so check billable
      If Datar(j, 13) = "Billable" Then
     [U] [B] Results(j, 1) = Coder(i, 3)
      Else
       Results(j, 1) = Coder(i, 4)[/B][/U]
      End If
     Exit For
    End If
   Next i
 Next j
' write the results out
 Range(.Cells(1, 16), .Cells(Lastcode, 16)) = Results
End With
 
End Sub

It also seems to return a "not found" for every row as the result in column P

 

[offthelip]

Can you use the debugger to find out which variable has the subscript out of range. you can do this by hover over the variable when the debugger stops.
When you have done that ope n the locals window ans see what you have got in that variable and see if it ties up with what is on the worksheet.

 

[Lefemmenikita]

This is what I get from checking the debugger

I have checked the original lookup sheet ('coding') and that value is definitely in the sheet.

 

[offthelip]

Thanks that gave me the clue to work out where my error was: I was using the wrong variable for the last row in the results variable: so change:

lastdata = .Cells(Rows.Count, "A").End(xlUp).Row 
Datar = Range(.Cells(1, 1), .Cells(lastdata, 13))
 
 Range(.Cells(2, 16), .Cells([COLOR=#ff0000]Lastcode[/COLOR], 16)) = "Not found"
 Results = Range(.Cells(1, 16), .Cells([COLOR=#ff0000]Lastcode[/COLOR], 16))

to:

lastdata = .Cells(Rows.Count, "A").End(xlUp).Row
 
Datar = Range(.Cells(1, 1), .Cells(lastdata, 13))
 
 Range(.Cells(2, 16), .Cells([COLOR=#ff0000]Lastdata[/COLOR], 16)) = "Not found"
 Results = Range(.Cells(1, 16), .Cells([COLOR=#ff0000]Lastdata[/COLOR], 16))

 

[Lefemmenikita]

Thanks. Tried it now and I can see that it seems to be coding it correctly only up to row 44 with every thing else showing as 'not found'

Incidentally, the 'coding' tab with the lookups only has 44 rows.

This suggests to me that the range it is looking up the codes in is not fixed.

i.e. similar to a vlookup where the lookup range is not absolute with a $ sign in front of the number. Copying the formula down changes the range of lookup. This seems to be doing the same (at least that is my wild guess based on what I am seeing)

 

[offthelip]

this is not like copying vlookup down at all. The way the code works is forst we detect how many rows with data in them on Coding sheet with this code:

With Worksheets("Coding")

Lastcode = .Cells(Rows.Count, "A").End(xlUp).Row

we then load all of this data in to a varinat array :

 Coder = Range(.Cells(1, 1), .Cells(Lastcode, 4))

we then do a similar thing with the data on the "data" sheet:

 With Worksheets("Data")

 Lastdata = .Cells(Rows.Count, "A").End(xlUp).Row
 Datar = Range(.Cells(1, 1), .Cells(Lastdata, 13))

we are then doing a double loop going down the data sheet one at a time to the lastdat row and for each of these loop through the coder sheet:

For j = 2 To Lastdata
  For i = 2 To Lastcode

So if you have 44 rows in the coder sheet Lastcode should be 44, and Lastdata should be equal to the number of rows on the data sheet.
Have you made the changes I suggested in post 15 correctly
this is what your code should look like:

Sub TEST()


With Worksheets("Coding")
' I assume:
'AcountDescription in column B
'Acount code in column A
' Billable value in column C
' Nonbillable Value in column D
 
 Lastcode = .Cells(Rows.Count, "A").End(xlUp).Row
 Coder = Range(.Cells(1, 1), .Cells(Lastcode, 4))
End With
With Worksheets("Data")
' I assume:
'AcountDescription in column A
'Acount code in column K
' Billable/Nonbillable  in column M
' the result in column P


 Lastdata = .Cells(Rows.Count, "A").End(xlUp).Row
 Datar = Range(.Cells(1, 1), .Cells(Lastdata, 13))
 
 Range(.Cells(2, 16), .Cells(Lastdata, 16)) = "Not found"
 Results = Range(.Cells(1, 16), .Cells(Lastdata, 16))


' I assume there is a header row so I start on row 2
For j = 2 To Lastdata
  For i = 2 To Lastcode
    If Coder(i, 1) = Datar(j, 11) And Coder(i, 2) = Datar(j, 1) Then
     ' code and description match so check billable
      If Datar(j, 13) = "Billable" Then
       Results(j, 1) = Coder(i, 3)
      Else
       Results(j, 1) = Coder(i, 4)
      End If
     Exit For
    End If
   Next i
 Next j
' write the results out
 Range(.Cells(1, 16), .Cells(Lastcode, 16)) = Results
End With
 
End Sub

 

[Lefemmenikita]

Thank you for all of your help and explanation.

This is what the results look like.

I definitely made the change, though just in case repasted the code. This is what I currently have:

Sub TEST()



With Worksheets("Coding")
' I assume:
'AcountDescription in column B
'Acount code in column A
' Billable value in column C
' Nonbillable Value in column D
 
 Lastcode = .Cells(Rows.Count, "A").End(xlUp).Row
 Coder = Range(.Cells(1, 1), .Cells(Lastcode, 4))
End With
With Worksheets("Data")
' I assume:
'AcountDescription in column A
'Acount code in column K
' Billable/Nonbillable  in column M
' the result in column P




 Lastdata = .Cells(Rows.Count, "A").End(xlUp).Row
 Datar = Range(.Cells(1, 1), .Cells(Lastdata, 13))
 
 Range(.Cells(2, 16), .Cells(Lastdata, 16)) = "Not found"
 Results = Range(.Cells(1, 16), .Cells(Lastdata, 16))




' I assume there is a header row so I start on row 2
For j = 2 To Lastdata
  For i = 2 To Lastcode
    If Coder(i, 1) = Datar(j, 11) And Coder(i, 2) = Datar(j, 1) Then
     ' code and description match so check billable
      If Datar(j, 13) = "Billable" Then
       Results(j, 1) = Coder(i, 3)
      Else
       Results(j, 1) = Coder(i, 4)
      End If
     Exit For
    End If
   Next i
 Next j
' write the results out
 Range(.Cells(1, 16), .Cells(Lastcode, 16)) = Results
End With
 
End Sub

 

[offthelip]

I can't help you with the information you have given, I suggest you debug it yourself. the code should be easy enough to understand. step through each line and check that it is picking the data up from the right place. As far as I can see it is doing exactly wht you asked for.

 

[Lefemmenikita]

Thank you for your help so far. I will have a go at seeing if I can add more rows to the test version and see where I am going wrong