VBA code for INDEX MATCH Functions

[Guest]

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&Station!$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,MATCH(" & NO4 & "&" & NO5 &
",Subdivision!$B$1:$B$3000&Subdivision!$C$1:$C$3000,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

 

[Rick Rothstein \(MVP - VB\)]

Any chance you can describe what you have and what you want to do with it in
words?

Rick

 

[Guest]

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