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

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

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