VBA - using the MATCH formula

[macroos]

Hi.

I want to use the MATCH formula here, but if more rows are added or deleted, the macros below will not work because the cells used in the formula are absolute (I think that's the word).

Note, in my table, the numbers have been sorted into negative numbers on top and positive number on bottom before doing this macros.

=MATCH(-E2,$F$6:$F$10,0) * Formula here will always be -E2 on here. $F$6:$F10 will change depending on the first positive value in column F to the last row
=MATCH(-F6,$G$2:$G$5,0) * -F6 will be the first cell that contains the positive number. It will always start with $G$2 but $G$5 has to change based on the last negative number in the column

A	B	C	D	E	F	G	H	I	J	K	L
2	data1	red		-5		-5
3	data2	red		-4		-4
4	data3	red		-3		-3
5	data4	green		-2		-2
6	data5	red		5	5
7	data6	green		4	4
8	data7	red		3	3
9	data8	red		2	2
10	data9	blue		1	1

<tbody>
</tbody>

Sub FormulaM()

    Dim formulaAll As Long, formulaPos As Long
    formulaAll = Cells(Rows.Count, "B").End(xlUp).Row
    formulaPos = Columns("E").Find("(*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1


    ' Based on above table, the formula on M2 would be =MATCH([COLOR=#ff0000]-E2,$F$6:$F$10[/COLOR],0) and would be copied from M3:M5
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=MATCH(-RC[-8],R47C6:R59C6,0)"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M" & formulaAll), Type:=xlFillDefault
    
     ' Based on above table, the formula on M6 would be =MATCH([COLOR=#ff0000]-F6,$G$2:$G$5[/COLOR],0) and would be copied from M7:M10
    Range("M47").Select
    ActiveCell.FormulaR1C1 = "=MATCH(-RC[-7],R2C7:R46C7,0)"
    Range("M47").Select
    Selection.AutoFill Destination:=Range("M" & formulaPos & ":M" & formulaAll), Type:=xlFillDefault

    ...

End Sub

 

[Peter_SSs]

Would you consider using the same formula for all the rows as per the code below?
Note too that you can enter all the formulas at once without selecting anything and without AutoFill.

Test in a copy of your workbook.

Sub FormulaM_v2()
    Dim formulaAll As Long, formulaPos As Long
    
    formulaAll = Cells(Rows.Count, "B").End(xlUp).Row
    formulaPos = Columns("E").Find("(*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1
    
    Range("M2:M" & formulaAll).FormulaR1C1 = "=MATCH(-RC[-8],IF(RC[-8]<0,R" & formulaPos & "C[-7]:R" & formulaAll & "C[-7],R2C[-6]:R" & formulaPos - 1 & "C[-6]),0)"
End Sub

 

[macroos]

It works! Thanks for your help!

 

[Peter_SSs]

It works! Thanks for your help!

You're welcome. Thanks for letting us know.