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