INDEX multiple MATCH with VBA separate sheets.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,113
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
ABCDEFG
4cdt1col2col3col4typecatVal
5cdt1type1cat110
6cdt1type1cat295
7cdt1type1cat311
8cdt1type4cat412
9cdt1type1cat5100
10cdt1type1cat622
11cdt1type2cat140
12cdt1type5cat256
13cdt1type2cat313
14cdt1type2cat455
15cdt1type2cat553
16cdt1type2cat615
17cdt1type3cat122
18cdt1type3cat386
19cdt1type3cat484
20cdt1type3cat577
21cdt1type3cat630
22cdt1type4cat294
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
ABCDE
1Titlecdt1
2
3
4
5
6table1cat1cat2cat3cat4
7type1109511 
8type240 1355
9
10
11
12
13
14Table2cat1cat2cat3
15type322 86
16type4 94 
results
Cell Formulas
RangeFormula
B7:E8B7=IFERROR(INDEX(Tbl_Source[Val],MATCH(1,($B$1=Tbl_Source[cdt1])*($A7=Tbl_Source[type])*(B$6=Tbl_Source[cat]),0)),"")
B15:D16B15=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"
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
Create a standard module. Paste this code in it:
VBA Code:
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
 
Last edited by a moderator:
Upvote 1
Solution
@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:
VBA Code:
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:
VBA Code:
          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:
VBA Code:
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:
VBA Code:
          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.
 
Upvote 0
Addendum:
VBA Code:
          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
 
Upvote 0
Will post the solution I came up with:
VBA Code:
          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?
 
Upvote 0
I am happy it did work for you (y) 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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top