How can I condense this code and also include line breaks please.

[S.H.A.D.O.]

Good evening,

Is there any way that I can condense the code below and also include line breaks please.

Option Explicit
Option Base 1

Sub Convert()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With

'   Convert values back to formulas.
    Range("J5").Formula = "=CONCATENATE(""1 = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53,1),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53,1),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Range("J6").Formula = "=CONCATENATE(""2 = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53,2),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53,2),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Range("J7").Formula = "=CONCATENATE(""3 = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53,3),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53,3),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Range("J8").Formula = "=CONCATENATE(""4 = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53,4),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53,4),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Range("J9").Formula = "=CONCATENATE(""5 = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53,5),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53,5),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Range("J10").Formula = "=CONCATENATE(""6 = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53,6),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53,6),'Main Statistics'!S$7:S$53,0)),""]"",)"

    Range("J13").Formula = "=CONCATENATE(""1 = "",INDEX('Plus 1 Statistics'!B$7:B$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,1),'Plus 1 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 1 Statistics'!M$7:M$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,1),'Plus 1 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J14").Formula = "=CONCATENATE(""2 = "",INDEX('Plus 1 Statistics'!B$7:B$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,2),'Plus 1 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 1 Statistics'!M$7:M$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,2),'Plus 1 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J15").Formula = "=CONCATENATE(""3 = "",INDEX('Plus 1 Statistics'!B$7:B$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,3),'Plus 1 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 1 Statistics'!M$7:M$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,3),'Plus 1 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J16").Formula = "=CONCATENATE(""4 = "",INDEX('Plus 1 Statistics'!B$7:B$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,4),'Plus 1 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 1 Statistics'!M$7:M$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,4),'Plus 1 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J17").Formula = "=CONCATENATE(""5 = "",INDEX('Plus 1 Statistics'!B$7:B$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,5),'Plus 1 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 1 Statistics'!M$7:M$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,5),'Plus 1 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J18").Formula = "=CONCATENATE(""6 = "",INDEX('Plus 1 Statistics'!B$7:B$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,6),'Plus 1 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 1 Statistics'!M$7:M$53,MATCH(SMALL('Plus 1 Statistics'!S$7:S$53,6),'Plus 1 Statistics'!S$7:S$53,0)),""]"",)"

    Range("J21").Formula = "=CONCATENATE(""1 = "",INDEX('Plus 2 Statistics'!B$7:B$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,1),'Plus 2 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 2 Statistics'!M$7:M$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,1),'Plus 2 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J22").Formula = "=CONCATENATE(""2 = "",INDEX('Plus 2 Statistics'!B$7:B$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,2),'Plus 2 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 2 Statistics'!M$7:M$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,2),'Plus 2 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J23").Formula = "=CONCATENATE(""3 = "",INDEX('Plus 2 Statistics'!B$7:B$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,3),'Plus 2 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 2 Statistics'!M$7:M$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,3),'Plus 2 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J24").Formula = "=CONCATENATE(""4 = "",INDEX('Plus 2 Statistics'!B$7:B$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,4),'Plus 2 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 2 Statistics'!M$7:M$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,4),'Plus 2 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J25").Formula = "=CONCATENATE(""5 = "",INDEX('Plus 2 Statistics'!B$7:B$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,5),'Plus 2 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 2 Statistics'!M$7:M$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,5),'Plus 2 Statistics'!S$7:S$53,0)),""]"",)"
    Range("J26").Formula = "=CONCATENATE(""6 = "",INDEX('Plus 2 Statistics'!B$7:B$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,6),'Plus 2 Statistics'!S$7:S$53,0)),"" ["",INDEX('Plus 2 Statistics'!M$7:M$53,MATCH(SMALL('Plus 2 Statistics'!S$7:S$53,6),'Plus 2 Statistics'!S$7:S$53,0)),""]"",)"

    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Thanks very much in advance.

 

[sheetspread]

A for/next loop i=1 to 6 will give you the counters for the formula, also have a for i = 5 to 21 step 8 to skip the 11/12 & 19/20.

 

[S.H.A.D.O.]

Thanks for the reply sheetspread,

I tried this but to no avail...

   Dim i As Long

   For i = 1 To 6
        Range("J5:J10").Formula = "=CONCATENATE("" & i & "" = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53, & i & ""),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53, & i & ""),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Next i

Thanks in advance.

 

[S.H.A.D.O.]

I have got a bit further using this...

    For i = 1 To 6
        Range("J5:J10").Formula = "=CONCATENATE(" & i & " & "" = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53," & i & "),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53," & i & "),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Next i

...but it only shows the last one of 6 in all the cells J5:J10.

Thanks in advance.

 

[sheetspread]

Try:

Sub test()
Dim i%, j%
For i = 0 To 16 Step 8
For j = 1 To 6
Range("j" & i + j + 4).Value = "=CONCATENATE(""" & j & " = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53," & j & "),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53," & j & "),'Main Statistics'!S$7:S$53,0)),""]"",)"
Next j
Next i
End Sub

 

[sheetspread]

I noticed you're switching tabs also, which requires something like:

Sub test()
Dim i%, j%, k As Variant
k = Array("Main", "Plus 1", "Plus 2")
For i = 0 To 16 Step 8
For j = 1 To 6
Range("j" & i + j + 4).Value = "=CONCATENATE(""" & j & " = "",INDEX('" & k(i / 8) & " Statistics'!B$7:B$53,MATCH(SMALL('" & k(i / 8) & " Statistics'!S$7:S$53," & j & "),'" & k(i / 8) & " Statistics'!S$7:S$53,0)),"" ["",INDEX('" & k(i / 8) & " Statistics'!M$7:M$53,MATCH(SMALL('" & k(i / 8) & " Statistics'!S$7:S$53," & j & "),'" & k(i / 8) & " Statistics'!S$7:S$53,0)),""]"",)"
Next j
Next i
End Sub

 

[S.H.A.D.O.]

Thanks very much sheetspread, it is appreciated.

I changed your code slightly to...

    For i = 1 To 6
        Range("J" & i + 4).Value = "=CONCATENATE(""" & i & " = "",INDEX('Main Statistics'!B$7:B$53,MATCH(SMALL('Main Statistics'!S$7:S$53," & i & "),'Main Statistics'!S$7:S$53,0)),"" ["",INDEX('Main Statistics'!M$7:M$53,MATCH(SMALL('Main Statistics'!S$7:S$53," & i & "),'Main Statistics'!S$7:S$53,0)),""]"",)"
    Next i

I had to take out the For i = 0 To 16 Step 8 part because the first block uses the WorkSheet Main Statistics, and the other two blocks use the WorkSheets Plus 1 Statistics and Plus 2 Statistics respectfully, although the ranges and the formulas are identical, all except the WorkSheet names of course.

I will get round this by just replicating the code above to accomodate this, with the WorkSheet names changed.

Thanks again sheetspread, you're a diamond .

 

[S.H.A.D.O.]

Applogies sheetspread, I must have been compiling my above post as you posted your new code.
I ran it but it came up with a subscript out of range error.

 

[sheetspread]

Because you're using Base 1. If you can't change it to 0 (default) for whatever reason, then the formula would have k(1+ i/8) instead

 

[S.H.A.D.O.]

I can't thank you enough sheetspread, I changed the code to Option Base 0, and then changed the code incorporating the new formula, and they BOTH worked perfectly, thank you.