create a macro with the match lookup_array is changing everyday

[Asraf]

Dear all,

I have a problem to create a macro bcos my data for lookup_array will be add
up everyday.


Sub Macro1()

ActiveCell.FormulaR1C1 = "=MATCH(RC[1],R1C[2]:R10C[2],0)"

End Sub

 

[Jacob Skaria]

Try
ActiveCell.FormulaR1C1 = "=MATCH(RC[1],C[2],0)"

OR

Dim lngRow As Long
lngRow = Cells(Rows.Count, ActiveCell.Offset(, 2).Column).End(xlUp).Row
ActiveCell.FormulaR1C1 = "=MATCH(RC[1],R1C[2]:R" & lngRow & "C[2],0)"

 

[OssieMac]

When you record your macro, instead of selecting C1:C10, Click the column Id
at the top of the column to select the entire column for the Lookup_Array.

 

[ozgrid.com]

Use a Dynamic Named Range
http://www.ozgrid.com/Excel/DynamicRanges.htm