How to speed up arrays in vba

M

mewashoo

New Member
Joined
Jul 19, 2017
Messages
10
Code:
Hello All

It's my first post on this forum, hello everyone!!!!

I was wondering if anyone know any way to speed up below code a little. It's exhausting RAM quite often especially if more than 1 spreadsheet I'd opened.

Code: 
Sub Recalculate()
'
' Recalculate Macro
'

'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlAutomatic

    'Clear content
    Range("C5:AP54").ClearContents
    Range("C5:AP54").ClearComments

    'C5 - Reactive Actions
    Range("C5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-2]:C[8],SMALL(IF(Sheet1!C[11]=""Reactive"",IF((Area=Sheet1!C[-2]),IF((Sheet1!C[53]=""Open Actions""),ROW(Sheet1!C[-2])-MIN(ROW(Sheet1!C[-2]))+1,"""")),""""),ROW(R[-4]C[3])),11),"""")"
    Selection.AutoFill Destination:=Range("C5:C54"), Type:=xlFillDefault
    Range("C5:C54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'D5
    Range("D5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[2], MATCH(RC[-1],Sheet1!C[7],0)),"""")"
    Selection.AutoFill Destination:=Range("D5:D54"), Type:=xlFillDefault
    Range("D5:D54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'E6
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[4], MATCH(RC[-2],Sheet1!C[6],0)),"""")"
    Selection.AutoFill Destination:=Range("E5:E54"), Type:=xlFillDefault
    Range("E5:E54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'F6
    Range("F5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[60], MATCH(RC[-3],Sheet1!C[5],0)),"""")"
    Selection.AutoFill Destination:=Range("F5:F54"), Type:=xlFillDefault
    Range("F5:F54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'G5
    Range("G5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("G5:G54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'H5 - Planned Actions
    Range("H5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Extra Works"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-1])),11),"""")"
    Selection.AutoFill Destination:=Range("H5:H54"), Type:=xlFillDefault
    Range("H5:H54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'I5
    Range("I5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-3], MATCH(RC[-1],Sheet1!C[2],0)),"""")"
    Selection.AutoFill Destination:=Range("I5:I54"), Type:=xlFillDefault
    Range("I5:I54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'J5
    Range("J5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-1], MATCH(RC[-2],Sheet1!C[1],0)),"""")"
    Selection.AutoFill Destination:=Range("J5:J54"), Type:=xlFillDefault
    Range("J5:J54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'K5
    Range("K5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[55], MATCH(RC[-3],Sheet1!C,0)),"""")"
    Selection.AutoFill Destination:=Range("K5:K54"), Type:=xlFillDefault
    Range("K5:K54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'L5
    Range("L5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("L5:L54"), Type:=xlFillDefault

    'M5 - Extra Works Actions
    Range("M5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C11,SMALL(IF(Sheet1!C14=""Planned"",IF((Area=Sheet1!C1),IF((Sheet1!C56=""Open Actions""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-6])),11),"""")"
    Selection.AutoFill Destination:=Range("M5:M54"), Type:=xlFillDefault
    Range("M5:M54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'N5
    Range("N5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-8], MATCH(RC[-1],Sheet1!C[-3],0)),"""")"
    Selection.AutoFill Destination:=Range("N5:N54"), Type:=xlFillDefault
    Range("N5:N54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'O5
    Range("O5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-6], MATCH(RC[-2],Sheet1!C[-4],0)),"""")"
    Selection.AutoFill Destination:=Range("O5:O54"), Type:=xlFillDefault
    Range("O5:O54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'P5
    Range("P5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[50], MATCH(RC[-3],Sheet1!C[-5],0)),"""")"
    Selection.AutoFill Destination:=Range("P5:P53"), Type:=xlFillDefault
    Range("P5:P54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Q5
    Range("Q5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("Q5:Q54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'R5 - Ambers
    Range("R5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Amber"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-4]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-11])),11),"""")"
    Selection.AutoFill Destination:=Range("R5:R54"), Type:=xlFillDefault
    Range("R5:R54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'S5
    Range("S5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-13], MATCH(RC[-1],Sheet1!C[-8],0)),"""")"
    Selection.AutoFill Destination:=Range("S5:S54"), Type:=xlFillDefault
    Range("S5:S54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'T5
    Range("T5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-2],Sheet1!C[-9],0)),"""")"
    Selection.AutoFill Destination:=Range("T5:T54"), Type:=xlFillDefault
    Range("T5:T54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'U5
    Range("U5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[13], MATCH(RC[-3],Sheet1!C[-10],0)),"""")"
    Selection.AutoFill Destination:=Range("U5:U54"), Type:=xlFillDefault
    Range("U5:U54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'V5
    Range("V5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("V5:V54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'W5 - 3 Day Ambers
    Range("W5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""3 Day Ambers"",IF((Area=Sheet1!C1),IF(NOT(Sheet1!C[-9]=""Planned""),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,"""")),""""),ROW(R[-4]C[-16])),11),"""")"
    Selection.AutoFill Destination:=Range("W5:W54"), Type:=xlFillDefault
    Range("W5:W54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'X5
    Range("X5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-18], MATCH(RC[-1],Sheet1!C[-13],0)),"""")"
    Selection.AutoFill Destination:=Range("X5:X54"), Type:=xlFillDefault
    Range("X5:X54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Y5
    Range("Y5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-16], MATCH(RC[-2],Sheet1!C[-14],0)),"""")"
    Selection.AutoFill Destination:=Range("Y5:Y54"), Type:=xlFillDefault
    Range("Y5:Y54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'Z5
    Range("Z5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[8], MATCH(RC[-3],Sheet1!C[-15],0)),"""")"
    Selection.AutoFill Destination:=Range("Z5:Z54"), Type:=xlFillDefault
    Range("Z5:Z54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AA5
    Range("AA5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AA5:AA54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AB5 - Red
    Range("AB5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C60=""Red"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-21])),11),"""")"
    Selection.AutoFill Destination:=Range("AB5:AB54"), Type:=xlFillDefault
    Range("AB5:AB54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'AC5
    Range("AC5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-23], MATCH(RC[-1],Sheet1!C[-18],0)),"""")"
    Selection.AutoFill Destination:=Range("AC5:AC54"), Type:=xlFillDefault
    Range("AC5:AC54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AD5
    Range("AD5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-21], MATCH(RC[-2],Sheet1!C[-19],0)),"""")"
    Selection.AutoFill Destination:=Range("AD5:AD54"), Type:=xlFillDefault
    Range("AD5:AD54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    'AE5
    Range("AE5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[3], MATCH(RC[-3],Sheet1!C[-20],0)),"""")"
    Selection.AutoFill Destination:=Range("AE5:AE54"), Type:=xlFillDefault
    Range("AE5:AE54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AF5
    Range("AF5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AF5:AF54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AK5 - Freebar
    Range("AK5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C1:C60,SMALL(IF(Sheet1!C33=""FREE"",IF((Area=Sheet1!C1),ROW(Sheet1!C1)-MIN(ROW(Sheet1!C1))+1,""""),""""),ROW(R[-4]C[-30])),11),"""")"
    Selection.AutoFill Destination:=Range("AK5:AK54"), Type:=xlFillDefault
    Range("AK5:AK54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'AL5
    Range("AL5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-32], MATCH(RC[-1],Sheet1!C[-27],0)),"""")"
    Selection.AutoFill Destination:=Range("AL5:AL54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AM5
    Range("AM5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-30], MATCH(RC[-2],Sheet1!C[-28],0)),"""")"
    Selection.AutoFill Destination:=Range("AM5:AM54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AO5
    Range("AO5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AO5:AO54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'AP5 - STAT PPM
    Range("AP5").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Sheet1!C[-41]:C[-31],SMALL(IF(Sheet1!C[-41]=Area,IF(Sheet1!C[-28]=""Planned"",IF((Sheet1!C[-27]=""Statutory""),IF((Sheet1!C[10]<Next),IF((Sheet1!C[10]>Last),ROW(Sheet1!C[-41])-MIN(ROW(Sheet1!C[-41]))+1,"""")))),""""),ROW(R[-4]C[-35])),11),"""")"
    Selection.AutoFill Destination:=Range("AP5:AP54"), Type:=xlFillDefault
    Range("AP5:AP54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
    Application.CutCopyMode = False

    'AQ5
    Range("AQ5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-37], MATCH(RC[-1],Sheet1!C[-32],0)),"""")"
    Selection.AutoFill Destination:=Range("AQ5:AQ54"), Type:=xlFillDefault
    Range("AQ5:AQ54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AR5
    Range("AR5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-35], MATCH(RC[-2],Sheet1!C[-33],0)),"""")"
    Selection.AutoFill Destination:=Range("AR5:AR54"), Type:=xlFillDefault
    Range("AR5:AR54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AS5
    Range("AS5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Sheet1!C[-11], MATCH(RC[-3],Sheet1!C[-34],0)),"""")"
    Selection.AutoFill Destination:=Range("AS5:AS54"), Type:=xlFillDefault
    Range("AS5:AS54").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'AT5
    Range("AT5").Select
    ActiveCell.FormulaR1C1 = " "
    Selection.AutoFill Destination:=Range("AT5:AT54"), Type:=xlFillDefault
    Application.CutCopyMode = False

    'borders
    Range("C5:F54, H5:K54, M5:P54, R5:U54, W5:Z54, AB5:AE54, AG5:AI54, AK5:AN54, AP5:AS54").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    'end
    Rows("5:54").Select
    Selection.RowHeight = 12
    Range("F5").Select
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
    MsgBox "All ok."

End Sub
 
Sort by date Sort by votes
Code does work and it's fast, but instead of looking for next cell to index it just fills all cells with same value.
It seems like I am only able to achieve this effect by autofilling column.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
dfsobral said:
try it and say something
Click to expand...

I am afraid you are hitting the same problem as with my code, if you look at the formula in column H you will see that the Row(G1) isn't incrementing in the array formula.
 
Upvote 0
There are other ways of putting in the array but none are fast. See Colin Legg's blog below.

https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

I guess only way is to somehow achieve the same without array
Click to expand...

It would be a better solution but not something I wish to try and do.

It might be worth the OP posting the actual formulas (with a space after any < symbol) and seeing if one of the formula experts can come up with non array versions.

P.S. the OP could also do with answering Norie's questions in case he has thought of something.
 
Last edited:
Upvote 0
Oops the OP has answered Nories questions and I missed it... apologies.
 
Upvote 0
Code: 
[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64, bgcolor: transparent"][FONT=Calibri][SIZE=2][COLOR=#000000]=IFERROR(INDEX(Sheet1!B:L,SMALL(IF(Sheet1!O:O="Reactive",IF((Area=Sheet1!B:B),IF((Sheet1!BE:BE="Open Actions"),ROW(Sheet1!B:B)-MIN(ROW(Sheet1!B:B))+1,"")),""),ROW(G2)),11),"")[/COLOR][/SIZE][/FONT][/TD]
 [/TR]
</tbody>[/TABLE]

This is one of arrays. They are all similar. I have been trying different variations of index small if formulas with no success. Either it's returning error #num/#value or value which definitely doesn't match my expectations lol.

i'm almost sure it can't be done without array.

Even simplest index isn't returning right value.
Code: 
=INDEX(Sheet1!K:K,(SMALL(Sheet1!N:N="Reactive",1)),)

I guess I don't really understand SMALL function
 
Upvote 0
@mewashoo, just a few suggestions...

1) most importantly... start a new thread, it shouldn't be breaking any rules as you are now asking a different question i.e. how to amend the formulas.
The reasons to do this are first of all it will show as a zero reply post and so you will probably get some new eyes looking at it and this thread is so long now that the formula specialists probably won't look at it.

2) your second formula appears to be incomplete. this is probably because you have a < sign followed by a letter which is being treated as an HTML tag, either put a space after the < sign or after pasting the formula replace it with &lt ; (without the space) wherever you have a < in the formula.

3) When you post the formulas give as much info as possible about the ranges and consider posting some usable screenshots (see my signature block for some links on how to do this), avoid posting images like JPegs as they can't be copied into the cells and so it will discourage some members from working on the problem.

Hope that helps some.
 
Upvote 0
Thanks Mark,

I have started new topic now, just waiting for approval from Admin and we shall see.

Thanks All for your input.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
ActiveSheet. Paste error
Replies
1
Views
67
RoryA
RoryA
P
  • Question
Help with Vba Code
Replies
3
Views
194
Coyotex3
C
C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
92
myall_blues
myall_blues
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
338
kevin9999
K
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
111
CapRavOr
C

Forum statistics

Threads
1,215,663
Messages
6,126,097
Members
449,291
Latest member
atfoley16

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top