| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for INDEX MATCH Functions
Can anyone help me here ... I haver a worksheet with 10000+ rows and I have
used the vba below to pick up a unique reference, however it relys on ActiveCell.FormulaArray = operation that updates a total of 6 cells before concatenating them by means of a simple cell formula = 60000+ events My question is how do I better code this into VBA to simply supply the end concatenated result, I am struggling to find any alternative Many Thanks In advance -- Alan MYCODE no1 = "$k$" & CurrentRow No2 = "$j$" & CurrentRow NO3 = "$i$" & CurrentRow NO4 = "$h$" & CurrentRow NO5 = "$g$" & CurrentRow NO6 = "$L$" & CurrentRow Dim indexarray1 As String 'stationname indexarray1 = "=INDEX(Station!B:E,MATCH(" & NO3 & "&" & NO4 & "&" & NO5 & ",Station!$B$1:$B$3000&Station!$C$1:$C$3000&Statio n!$D$1:$D$3000,0),4)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'subdivdeptname indexarray1 = "=INDEX(Subdivision!B:D,MATCH(" & NO4 & "&" & NO5 & ",Subdivision!$B$1:$B$3000&Subdivision!$C$1:$C$300 0,0),3)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'substationname indexarray1 = "=INDEX(Substation!B:F,MATCH(" & No2 & "&" & NO3 & "&" & NO4 & "&" & NO5 & ",Substation!$B$1:$B$3000&Substation!$C$1:$C$3000& Substation!$D$1:$D$3000&Substation!$E$1:$E$3000,0) ,5)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'depatrmentname indexarray1 = "=INDEX(Dept!B:G,MATCH(" & no1 & "&" & No2 & "&" & NO3 & "&" & NO4 & "&" & NO5 & ",Dept!$B$1:$B$3000&Dept!$C$1:$C$3000&Dept!$D$1:$D $3000&Dept!$E$1:$E$3000&Dept!$F$1:$F$3000,0),6)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'sectionname indexarray1 = "=INDEX(Section!B:E,MATCH(" & NO6 & "&" & NO4 & "&" & NO5 & ",Section!$B$1:$B$30&Section!$C$1:$C$30&Section!$D $1:$D$30,0),4)" ActiveCell.FormulaArray = indexarray1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA code for INDEX MATCH Functions
Any chance you can describe what you have and what you want to do with it in
words? Rick "Alan" wrote in message ... Can anyone help me here ... I haver a worksheet with 10000+ rows and I have used the vba below to pick up a unique reference, however it relys on ActiveCell.FormulaArray = operation that updates a total of 6 cells before concatenating them by means of a simple cell formula = 60000+ events My question is how do I better code this into VBA to simply supply the end concatenated result, I am struggling to find any alternative Many Thanks In advance -- Alan MYCODE no1 = "$k$" & CurrentRow No2 = "$j$" & CurrentRow NO3 = "$i$" & CurrentRow NO4 = "$h$" & CurrentRow NO5 = "$g$" & CurrentRow NO6 = "$L$" & CurrentRow Dim indexarray1 As String 'stationname indexarray1 = "=INDEX(Station!B:E,MATCH(" & NO3 & "&" & NO4 & "&" & NO5 & ",Station!$B$1:$B$3000&Station!$C$1:$C$3000&Statio n!$D$1:$D$3000,0),4)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'subdivdeptname indexarray1 = "=INDEX(Subdivision!B:D,MATCH(" & NO4 & "&" & NO5 & ",Subdivision!$B$1:$B$3000&Subdivision!$C$1:$C$300 0,0),3)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'substationname indexarray1 = "=INDEX(Substation!B:F,MATCH(" & No2 & "&" & NO3 & "&" & NO4 & "&" & NO5 & ",Substation!$B$1:$B$3000&Substation!$C$1:$C$3000& Substation!$D$1:$D$3000&Substation!$E$1:$E$3000,0) ,5)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'depatrmentname indexarray1 = "=INDEX(Dept!B:G,MATCH(" & no1 & "&" & No2 & "&" & NO3 & "&" & NO4 & "&" & NO5 & ",Dept!$B$1:$B$3000&Dept!$C$1:$C$3000&Dept!$D$1:$D $3000&Dept!$E$1:$E$3000&Dept!$F$1:$F$3000,0),6)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'sectionname indexarray1 = "=INDEX(Section!B:E,MATCH(" & NO6 & "&" & NO4 & "&" & NO5 & ",Section!$B$1:$B$30&Section!$C$1:$C$30&Section!$D $1:$D$30,0),4)" ActiveCell.FormulaArray = indexarray1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA code for INDEX MATCH Functions
Rick
Sorry I will try to explain I have to create a string value to allocate individuals to a group, however the information in the exisiting table is too long What I am doing is matching the multiple properties to a different db tables and then basically returning the primary key id which I convert to 4 characters and thus provides me with a string of 24 numbers which is unique to a group of individuals roles, which then allows me to group these together and provide a logical understandable name looking up the 24 digit string I hope that explains it somewhat the main purpose of my question is to find a vba method rather than placing formulas in a cell with the ActiveCell.Formula Array Function Many Thanks for your time -- Regards Alan "Rick Rothstein (MVP - VB)" wrote: Any chance you can describe what you have and what you want to do with it in words? Rick "Alan" wrote in message ... Can anyone help me here ... I haver a worksheet with 10000+ rows and I have used the vba below to pick up a unique reference, however it relys on ActiveCell.FormulaArray = operation that updates a total of 6 cells before concatenating them by means of a simple cell formula = 60000+ events My question is how do I better code this into VBA to simply supply the end concatenated result, I am struggling to find any alternative Many Thanks In advance -- Alan MYCODE no1 = "$k$" & CurrentRow No2 = "$j$" & CurrentRow NO3 = "$i$" & CurrentRow NO4 = "$h$" & CurrentRow NO5 = "$g$" & CurrentRow NO6 = "$L$" & CurrentRow Dim indexarray1 As String 'stationname indexarray1 = "=INDEX(Station!B:E,MATCH(" & NO3 & "&" & NO4 & "&" & NO5 & ",Station!$B$1:$B$3000&Station!$C$1:$C$3000&Statio n!$D$1:$D$3000,0),4)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'subdivdeptname indexarray1 = "=INDEX(Subdivision!B:D,MATCH(" & NO4 & "&" & NO5 & ",Subdivision!$B$1:$B$3000&Subdivision!$C$1:$C$300 0,0),3)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'substationname indexarray1 = "=INDEX(Substation!B:F,MATCH(" & No2 & "&" & NO3 & "&" & NO4 & "&" & NO5 & ",Substation!$B$1:$B$3000&Substation!$C$1:$C$3000& Substation!$D$1:$D$3000&Substation!$E$1:$E$3000,0) ,5)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'depatrmentname indexarray1 = "=INDEX(Dept!B:G,MATCH(" & no1 & "&" & No2 & "&" & NO3 & "&" & NO4 & "&" & NO5 & ",Dept!$B$1:$B$3000&Dept!$C$1:$C$3000&Dept!$D$1:$D $3000&Dept!$E$1:$E$3000&Dept!$F$1:$F$3000,0),6)" ActiveCell.FormulaArray = indexarray1 If ActiveCell.Text = "#N/A" Then ActiveCell.Value = "'0000" ActiveCell = ActiveCell.Offset(0, 1).Activate 'sectionname indexarray1 = "=INDEX(Section!B:E,MATCH(" & NO6 & "&" & NO4 & "&" & NO5 & ",Section!$B$1:$B$30&Section!$C$1:$C$30&Section!$D $1:$D$30,0),4)" ActiveCell.FormulaArray = indexarray1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| INDEX and MATCH functions | Excel Worksheet Functions | |||
| Index and Match functions I think | New Users to Excel | |||
| Row, Index, Match functions | Excel Discussion (Misc queries) | |||
| Index and Match Functions | Excel Programming | |||
| How do I use the Match and Index functions to look up a value tha. | Excel Worksheet Functions | |||
Linear Mode
