Date in MATCH formula

Hi Group,
I have some code to do a Match on a date, if the date is hard coded in the
formula it works just fine, but i am trying to get that date in via a
variable.

This works:
ActiveCell.FormulaR1C1 = "=MATCH(""1/25/99""+0,DateRange,-1)"
but his does not:
ActiveCell.FormulaR1C1 = "=MATCH(" & (Z1) & "+0,DateRange,-1)"
Z1 is the variable with the same date, but it fails to get the " " around
the date and fails.

The +0 is not being used currently, but is will be used to find an
appromiate date, later in the code, one year later. It is being used in a
table with about 5 years worth of data.

Thanks,

--
David

Just like you doubled up those quotations in your example that worked...

Dim Z1 As String
Z1 = "1/25/99"
ActiveCell.FormulaR1C1 = "=MATCH(""" & Z1 & """+0,DateRange,-1)"

Sometimes when doubling those quotes gets too much for my eyes, I use:

Dim Z1 As String
Z1 = "1/25/99"
ActiveCell.FormulaR1C1 _
= "=MATCH(" & chr(34) & Z1 & chr(34) & "+0,DateRange,-1)"



David wrote:
>
> Hi Group,
> I have some code to do a Match on a date, if the date is hard coded in the
> formula it works just fine, but i am trying to get that date in via a
> variable.
>
> This works:
> ActiveCell.FormulaR1C1 = "=MATCH(""1/25/99""+0,DateRange,-1)"
> but his does not:
> ActiveCell.FormulaR1C1 = "=MATCH(" & (Z1) & "+0,DateRange,-1)"
> Z1 is the variable with the same date, but it fails to get the " " around
> the date and fails.
>
> The +0 is not being used currently, but is will be used to find an
> appromiate date, later in the code, one year later. It is being used in a
> table with about 5 years worth of data.
>
> Thanks,
>
> --
> David

--

Dave Peterson

David:

> ActiveCell.FormulaR1C1 = "=MATCH(" & (Z1) & "+0,DateRange,-1)"

ActiveCell.FormulaR1C1 = "=MATCH(" & Chr(34) & Z1 & Chr(34) &
"+0,DateRange,-1)"

--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"David" wrote:

> Hi Group,
> I have some code to do a Match on a date, if the date is hard coded in the
> formula it works just fine, but i am trying to get that date in via a
> variable.
>
> This works:
> ActiveCell.FormulaR1C1 = "=MATCH(""1/25/99""+0,DateRange,-1)"
> but his does not:
> ActiveCell.FormulaR1C1 = "=MATCH(" & (Z1) & "+0,DateRange,-1)"
> Z1 is the variable with the same date, but it fails to get the " " around
> the date and fails.
>
> The +0 is not being used currently, but is will be used to find an
> appromiate date, later in the code, one year later. It is being used in a
> table with about 5 years worth of data.
>
> Thanks,
>
> --
> David

Thank you very much, worked like a charm.
--
David


"David" wrote:

> Hi Group,
> I have some code to do a Match on a date, if the date is hard coded in the
> formula it works just fine, but i am trying to get that date in via a
> variable.
>
> This works:
> ActiveCell.FormulaR1C1 = "=MATCH(""1/25/99""+0,DateRange,-1)"
> but his does not:
> ActiveCell.FormulaR1C1 = "=MATCH(" & (Z1) & "+0,DateRange,-1)"
> Z1 is the variable with the same date, but it fails to get the " " around
> the date and fails.
>
> The +0 is not being used currently, but is will be used to find an
> appromiate date, later in the code, one year later. It is being used in a
> table with about 5 years worth of data.
>
> Thanks,
>
> --
> David