TEXT formula help

[skull_eagle]

Hi,

I've written the below formula and it work fine, It works out the Min and max years in a range eg. 1998 - 2000

The problem is when the min and max are the same I end up with 1998 - 1998

Is there any way I can make an adjustment to just show the 1 year when this occurs.

    ActiveCell.FormulaR1C1 = _
        "=TEXT(MIN(R[1]C:R[20]C), ""yyyy"") & "" - "" & TEXT(MAX(R[1]C:R[20]C), ""yyyy"")"

Thanks

 

[Hermanito]

Why use TEXT and not just YEAR? Makes it somewhat shorter, assuming of course the range you use contains dates!

Try this:

ActiveCell.FormulaR1C1 = _
"=IF(YEAR(MIN(R[1]C:R[20]C))=YEAR(MAX(R[1]C:R[20]C));YEAR(MIN(R[1]C:R[20]C)); YEAR(MIN(R[1]C:R[20]C)) & "" - "" & YEAR(MAX(R[1]C:R[20]C)))"

 

[skull_eagle]

Thanks for the response.

Unfortunately I can't get that code to work, I just keep getting a 'Run-Time error 1004' error.

Any ideas?


Thanks

 

[Hermanito]

Hmm, it fails for me as well...

I had tested the formula itself on a dummy range, then converted to VBA and your RC-style ranges...

For what range are you doing this?
Why are you using RC-style reference?

 

[skull_eagle]

I need the formula to run in cell B1 each time, I have no preference for style just as long as it works


Thanks

 

[Hermanito]

Found the reason: replace the semicolons with commas and it works

Excel is messy in that regard, imho, depending on the language of your version and if you're working on a worksheet or in VBA, you need a comma or a semicolon, or viceversa, and I never seem to get it right the first time

 

[skull_eagle]

Great, works perfectly. Thank you so much for your help and persistence

 

[Hermanito]

you're welcome...

the irony of the ,; issue is that the actual formula you end up with in the cell, again has semicolons, even though the string in VBA needs commas for it to work... go figure

 

[skull_eagle]

HA, that is funny.

It's amazing the trouble 1 wrong symbol can make.

 

[pgc01]

Hi

Just to clarify.

The properties .Formula and .FormulaR1C1 should be used with formulas with the English syntax.

You can, however, use formulas according to your regional settings. In that case, use instead the properties .FormulaLocal and .FormulaR1C1Local