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]

The only thing I can see wrong with the code is the declarations, the fact I is an integer might be the probelm
SO change the declarations to:

Dim LastMeasureDesc ' this defaults to varaint which works fine
Dim LastData
Dim j As Long
Dim i As Long

Dim Data
Dim Results
Dim MeasureDesc

 

[NScott85]

I update the declarations to what you show and it's still not iterating through the ValidMeasureDescription tab correctly.

Sub Test()

Dim LastMeasureDesc
Dim LastData
Dim j As Long
Dim i As Long

Dim Data
Dim Results
Dim MeasureDesc

With Worksheets("ValidMeasureDescriptions")
 LastMeasureDesc = .Cells(Rows.Count, "A").End(xlUp).Row
 MeasureDesc = Range(.Cells(1, 1), .Cells(LastMeasureDesc, 4))
End With


With Worksheets("Measure Fields")
 LastData = .Cells(Rows.Count, "A").End(xlUp).Row
 Data = Range(.Cells(1, 1), .Cells(LastData, 195))
 
 
 Range(.Cells(2, 195), .Cells(LastData, 195)) = "Not found"
   Results = Range(.Cells(1, 195), .Cells(LastData, 195))


' I assume there is a header row so I start on row 2
For j = 2 To LastData
  For i = 2 To LastMeasureDesc
  
Debug.Print "MeasureDesctiptionName: " & (MeasureDesc(i, 1))
Debug.Print "DataName: "; (Data(j, 189))

Debug.Print "MeasureDesctiptionType: " & (MeasureDesc(i, 2))
Debug.Print "DataType: " & (Data(j, 194))

    If MeasureDesc(i, 1) = Data(j, 189) And MeasureDesc(i, 2) = Data(j, 194) Then
     ' code and description match so check billable
       Results(j, 1) = MeasureDesc(i, 4)
      End If
     Exit For
   Next i
 Next j
 
' write the results out
 Range(.Cells(1, 195), .Cells(LastData, 195)) = Results
End With
 
End Sub

Even at the last rows of the Measure Fields tab, the Measure Description names and types aren't finding the match:
MeasureDesctiptionName: Advanced Power Strip-$10 Copay-Joint HEA
DataName: Showerhead-Gas DHW-Joint HEA
MeasureDesctiptionType:
DataType: VA

 

[NScott85]

ValidMeasureDescription Tab Layout

Old Name	Lookup Helper	Extra Column	New Name
Measure Name 1			New Measure Name 1
Measure Name 2			New Measure Name 2
Measure Name 3	DIM		New Measure Name 3
Measure Name 3	VA		New Measure Name 4

 

[offthelip]

I can't see anything wrong with the code, the fact is actually does complete some of it implies that there is something wrong with how it detects the last row of data. Have checked the values in
Lastdata and Lastmeasuredesc which are the two variables that control the loop. Use debug print or put a breakpoint in the code and check with debug or viewlocals If these are the correct values then the only other expanation is that the check:

If MeasureDesc(i, 1) = Data(j, 189) And MeasureDesc(i, 2) = Data(j, 194) Then

Fails after a certain number of rows which means the data isn't quite as you expect it

 

[NScott85]

As I am stepping through the debugger, I am never getting to

 Next i

If I move Next i higher into the code, I get Compile Error: Next without For

 

[offthelip]

Don't move the next i it is correct, you need to check the value in Lastdata and Lastmeasuredesc just before the loops

 

[NScott85]

Don't move the next i it is correct, you need to check the value in Lastdata and Lastmeasuredesc just before the loops

Oops, I did that before my last comment. They are correct.

 

[offthelip]

useing the debugger put a break point on the if statement and check the values there, that must be where it is failing

 

[NScott85]

This is what I changed.

Original Code:

If MeasureDesc(i, 1) = Data(j, 189) And MeasureDesc(i, 2) = Data(j, 194) Then
     ' code and description match so check billable
       Results(j, 1) = MeasureDesc(i, 4)
      End If
     Exit For
   Next i
 Next j

New Code:

If MeasureDesc(i, 1) = Data(j, 189) And MeasureDesc(i, 2) = Data(j, 194) Then
     ' code and description match so check billable
       Results(j, 1) = MeasureDesc(i, 4)

      End If
    Next i
 Next j

 

[offthelip]

I have just spotted that the exit for is in the wrong place it should be inside the if statement