Prev: Can this be done?
Next: Web Query Multiple Variables on a Mac
From: Asraf on 5 May 2010 02:47
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

From: Jacob Skaria on 5 May 2010 03:01
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)"


--
Jacob (MVP - Excel)


"Asraf" wrote:

> 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
>
From: OssieMac on 5 May 2010 03:01
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.

--
Regards,

OssieMac


"Asraf" wrote:

> 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
>
From: ozgrid.com on 5 May 2010 03:08
Use a Dynamic Named Range
http://www.ozgrid.com/Excel/DynamicRanges.htm


--
Regards
Dave Hawley
www.ozgrid.com
"Asraf" <Asraf(a)discussions.microsoft.com> wrote in message
news:233C7671-1C48-4745-8553-24905CAB4DFD(a)microsoft.com...
> 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
>

 | 
Pages: 1
Prev: Can this be done?
Next: Web Query Multiple Variables on a Mac