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