Replies

Elegant bit of code Steve.

Hello RG

I saw an argument some time back professing that if not dealing with whole numbers, there was no advantage to using single over double when declaring a number. Do you have any thoughts on this?

Hi Wassim

I suspect that you do not wish numbers repeated within the range of interest. I suspect that the solutions shown to date will allow this situation if the user enters anything but the next higher number. I offer the following concept.

[*]Assume that your input range is $A$10:$L$10
[*]In cell A10 enter the following validation formula:

[*]=1+MAX($B10:$L10)

[*]In cell L10 enter the following validation formula

[*]=1+MAX($A10:$K10)

[*]In cell B10 enter the following validation formula

[*]=1+MAX(MAX($A10:A10),MAX(C10:$L$10))

[*]Copy cell B10 and paste it into cells C10:K10
[/LIST]
With this approach, once a number is entered, it can only be deleted or changed to the next acceptable number. Having taken either of these actions, the number that was changed will remain unused in the series unless an Undo (Ctrl + Z), action is invoked as the next step. Having deleted the number from a cell, it can then be populated with the next acceptable number.

Be cautious; the first sixty days in the Excel calendar are in error. Lotus123 mistakenly calculated that the year 1900 was a leap year. When Excel first launched, they advertised 100% compatibility with Lotus123 and consequently incorporated February 29 1900 (a day that didn’t exist), into their calendar. I assume that the error remains as a result of backward compatibility requirements.

XDate does not recognize this fictitious date (Feb. 29 1900).

Thank you Jefferson and Andrew

In another forum, Hans V offered me the following advice which nicely did the trick.

IrfanView will let you choose a color to become transparent when saving a .png,

I’ve opened several workbooks and all have Page Setup, Print Preview and Print greyed out in the file menu. ANy idea how I can fix this?
Thanks

What is the situation when attempting to print the blank worksheet immediately after launching Excel?
Do you have a problem when printing a Word document?

Which version/s of Excel will it work with?
Ben

It was composed in 2003, I believe that Hans has run it in 2007. It should work in all versions.

Thank you.

I will certainly give this a workout,

Thank you Jefferson and Retiree

FWIW

I have been advised to NOT attempt modifying existing menu bar items. They aren’t mine to modify.

See attached sample for what I am trying to accomplish.

Hi Corden

Here is a second example using VBA,

HTH

See attached sample for what I am trying to accomplish.

Hello Corden

Have you considered using a Pivot Table/Chart as shown in the attached sample?

Regards
Don

Not particularly. I prefer the former as it’s easy to watch what is going into the variable and it cuts down on the number of times you read properties of the range, but in performance terms I doubt there’s any real difference.

I was surprised to find that although your preferred approach appears to be taking an extra step; it consistently runs 2% faster than the shorter version which I identified.
Go figure.

Need more info:
How many years?
Which years?
Sample of date ranges needed?

I think that Corden’s request is satisfied with the attached.

Code:

    rngTo(1).Resize(UBound(varFormulas, 1), UBound(varFormulas, 2)).Formula = varFormulas

Thank you for that Rory.

Is there any advantage to either of the following snippets over the other?

Code:

    varFormulas = rngFrom.Formula
    rngTo(1).Resize(UBound(varFormulas, 1), UBound(varFormulas, 2)).Formula = varFormulas

Code:

    rngTo(1).Resize(rngFrom.Rows.Count, rngFrom.Columns.Count).Formula = rngFrom.Formula