|
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 |
Date in MATCH formula
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 |
Date in MATCH formula
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 |
Date in MATCH formula
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 |
| All times are GMT +1. The time now is 07:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com