INDEX multiple MATCH with VBA separate sheets.

[cyrilbrd]

This post is related to this thread.

Considering one source of data with multiple columns such as:

VBA-INDEX-MATCH-Based-on-Multiple-Criteria.xlsm
	A	B	C	D	E	F	G
4	cdt1	col2	col3	col4	type	cat	Val
5	cdt1				type1	cat1	10
6	cdt1				type1	cat2	95
7	cdt1				type1	cat3	11
8	cdt1				type4	cat4	12
9	cdt1				type1	cat5	100
10	cdt1				type1	cat6	22
11	cdt1				type2	cat1	40
12	cdt1				type5	cat2	56
13	cdt1				type2	cat3	13
14	cdt1				type2	cat4	55
15	cdt1				type2	cat5	53
16	cdt1				type2	cat6	15
17	cdt1				type3	cat1	22
18	cdt1				type3	cat3	86
19	cdt1				type3	cat4	84
20	cdt1				type3	cat5	77
21	cdt1				type3	cat6	30
22	cdt1				type4	cat2	94
data

And consider on a different sheet mutple tables that would be populated based on the information found in the Tbl_Source such as:

VBA-INDEX-MATCH-Based-on-Multiple-Criteria.xlsm
	A	B	C	D	E
1	Title	cdt1
2
3
4
5
6	table1	cat1	cat2	cat3	cat4
7	type1	10	95	11
8	type2	40		13	55
9
10
11
12
13
14	Table2	cat1	cat2	cat3
15	type3	22		86
16	type4		94
results

Cell Formulas
Range	Formula
B7:E8	B7	=IFERROR(INDEX(Tbl_Source[Val],MATCH(1,($B$1=Tbl_Source[cdt1])*($A7=Tbl_Source[type])*(B$6=Tbl_Source[cat]),0)),"")
B15:D16	B15	=IFERROR(INDEX(Tbl_Source[Val],MATCH(1,($B$1=Tbl_Source[cdt1])*($A15=Tbl_Source[type])*(B$14=Tbl_Source[cat]),0)),"")

What would be the best solution to populate the information from Tbl_Source column Val into each tables using VBA (or UDF).
The objective being to be able to populate the tables via VBA based on 3 conditions, "cdt", "type" and "cat"

 

[Legacy 143009]

Your solution seems to be OK. What are you expecting in advance?

 

[cyrilbrd]

Your solution seems to be OK. What are you expecting in advance?

Hi, as stated, the solution I am looking to populate the information from Tbl_Source column Val into each tables should be using VBA or an UDF that would allow me to identify the cells for the 3 conditions. Something along UDFIndex=(cdt1, type, cat) and would replace the INDEX MATCH formula.
OR a translation of the formulae presently used as a VBA(s) code(s).
I am unable to include the formula in the existing code due to a DEBUG issue.
One of the feature I am looking for is the ability to repopulate the tables with previous data.
So that tables feed the source (this part is working fine)
And the source can feed back the tables if the data is recalled.

 

[Legacy 143009]

Create a standard module. Paste this code in it:

Function UDFIndex(cdt As String, typ As String, cat As String)
  Dim cdtIndex As Integer, typeIndex As Integer, catIndex As Integer, valIndex As Integer
  Dim myTable As Variant
 
  With Worksheets("data").ListObjects("Tbl_Source")
  myTable = .DataBodyRange
  cdtIndex = .ListColumns("cdt").Index
  typeIndex = .ListColumns("type").Index
  catIndex = .ListColumns("cat").Index
  valIndex = .ListColumns("Val").Index
  End With
 
  For i = 1 To UBound(myTable, 1)
    If myTable(i, cdtIndex) = cdt And myTable(i, typeIndex) = typ And myTable(i, catIndex) = cat Then
      UDFIndex = myTable(i, valIndex)
      Exit Function
    End If
  Next
End Function

 

[Legacy 143009]

PS: I modified table header cdt1 to cdt.

 

[cyrilbrd]

@Flashbond yes not changing cdt1 to cdt was an omission on my side, thanks for correcting accordingly.
The UDF works as intended.
Question, in the recall method I am using, some formulae are kept, so the code would first check if the cdt exist, if no then a message would inform the user, if yes then proceed with the recall ,and here I was hoping to use something along:

WS.Range("C9").Formula = "=UDFIndex($B$3,$A9,C$8)"

Which works well for the intended purpose but this is only one cell, and I have several tables or group of cells with various type and cat.
This said, I could create as seen above for each table to ensure proper targeting of typ and cat STRINGS.
Further, I did not want the formula to appear I added the following:

          WS.Range("C9").Formula = "=UDFIndex($B$3,$A9,C$8)"
          WS.Range("C9").FormulaHidden = True

which works as intended (maybe a different method is preferable?)
My next question is I was thinking of using FormulaArray to paste the UDF in a range:

WS.Range("C9:I13").FormulaArray = "=UDFIndex($B$3,$A9,C$8)"

Upon Running the Sub I get a run-time error 1004 stating that it is unable to set the FormulaArray property of the Range class
So I used Autofill Destination and got a partial solution:

          WS.Range("C9").Formula = "=UDFIndex($B$3,$A9,C$8)"
          WS.Range("C9").FormulaHidden = True
          WS.Range("C9").AutoFill Destination:=Range("C9:I13")

Using this code would successfully get the values, hide the formulae but strangely did not copy all results in the specified range C9 to I13
Instead I got only C9, then D10 to I13.

I hope that what I wish to achieve is clear, use the created UDF to populate several tables if the user wishes to recall specific previously stored data.
Issue encountered thus far is my inability to properly use the UDF in FormulaArray and alternatively my inability to properly type the Autofill Destination to ensure that the whole range is populated.
Any insight would be appreciated.

 

[cyrilbrd]

Addendum:

          WS.Range("C9").Formula = "=UDFIndex($B$3,$A9,C$8)"
          WS.Range("C9").FormulaHidden = True
          Set fillrange1 = WS.Range("C9:I13")
          WS.Range("C9").AutoFill Destination:=fillrange1

Even if I would set the range, the fill would omit Row 9 and Column C except for C9

 

[cyrilbrd]

Will post the solution I came up with:

          WS.Range("C9").Formula = "=UDFIndex($B$3,$A9,C$8)"
          WS.Range("C9").FormulaHidden = True
          Set fillrange6 = WS.Range("C9:C13")
          WS.Range("C9").AutoFill Destination:=fillrange6

          WS.Range("C9:C13").Copy Destination:=WS.Range("D9:I13")

First the UDF is called and hidden
Second the code sets the first range "Horizontal" only and fill it up with agreed content
Last that range is copied over a new range.

So far it works and all cells are properly filled up, however may I ask if there would be a better way to do this?

 

[Legacy 143009]

I am happy it did work for you Thanks for the feedback.

Unfortunately, I don't have a clear answer for neither of your questions. I am not familiar with FormulaHidden property. I've never used it. Actually, I never needed it. If you are concerned about the user intervention then, I would lock the cell and protect the sheet with a password for further modifications.

For the second issue, it is a very strange behavior. Most of the times VBA recognizes the formula and auto adjusts reference addresses when you define a formula to a multi-cell .Range. Example : WS.Range("C9:13").Formula = "=UDFIndex($B$3,$A9,C$8)"

I hope these help. Maybe you should start a separate thread for better suggestions from others.