Getting Minimum and maximum level from a cell

[a94andwi]

Hello.

I have a chart that is a timeschedule
When I format the X-axis I have written one date in the minimum value and another date in the maximum value.

Is there any way that I can get these values from a cell that contains the dates?
Cell A1 should be the Minimum and Cell A2 should contain the maximum value.

Can I in a similar way controll the Major and Minor units?

I hope someone can help me.

/Anders

[MSP77079]

Is there any way that I can get these values from a cell that contains the dates?

The only way I know to do this is to use Visual Basic (macro). Turn on the macro recorder (Tools >> Macro >> Record New Macro). Do it once manually. Turn off the macro recorder. Find the macro that was just recorded (Tools >> Macro >> Macros; select the macro; select Edit). Replace the constants with references to the cells containing values.

[a94andwi]

I did like you told me:

Please Login or Register  to view this content.

It returns this error: Run-Time Error 13: Type mismatch.

In cell C1 there is : 2007-01-01
In cell E1 there is: 2008-06-30

It seems like I should convert my date to a 5 digit number.
How do I do that?

/Anders

[starguy]

Hello.

I have a chart that is a timeschedule
When I format the X-axis I have written one date in the minimum value and another date in the maximum value.

Is there any way that I can get these values from a cell that contains the dates?
Cell A1 should be the Minimum and Cell A2 should contain the maximum value.

Can I in a similar way controll the Major and Minor units?

I hope someone can help me.

/Anders

you can use MIN and MAX function to return minimum and maximum values from a range of cells.
MIN in A1
MAX in A2

[a94andwi]

Hello.

Unfortunately I can not use functions in this cell.

How do I convert 2007-01-01 to 39083 format?
If I get that I will manage.

/Anders

[MSP77079]

First, may I say, very good start!

Try using the DateValue function in VBA. It worked for me when I tested it just now.

Please Login or Register  to view this content.

[a94andwi]

Hello.

The datevalue function seems to be exactly what I am looking for.
Unfortunately it returns an error as well.
Have I done something wrong in the code below?
I have changed the name of the sheet to Settings and changed the cells to F1 & G1.


With ActiveChart.Axes(xlValue)
.MinimumScale = DateValue(Worksheets("Settings").Range("F1").Value)
.MaximumScale = DateValue(Worksheets("Settings").Range("G1").Value)
.MinorUnit = 1
.MajorUnit = 7
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

[MSP77079]

Your code looks fine (mine seems to have contained an error which you fixed, replacing Cells with Range).

I have tried to create an error with this code and have been unable to do so. Hmmm.

Two questions:
1. have you tried stepping through the code one line at a time (using F8) to determine exactly which line of code is creating the problem?

2. what is the error message?

[a94andwi]

Runtime error 13, type mismatch.

It marks this text yellow:
.MinimumScale = DateValue(Worksheets("Settings").Range("F1").Value )


How should I format 2007-01-01 from the start? Do I need to convert it to a text value with =TEXT("2007-01-01";"text")?

[MSP77079]

When I tested it, the code would accept

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

but not

Please Login or Register  to view this content.

I got a type mismatch on the latter (same as you).

It accepted the input whether it was formatted as Date or as Number or as Text.

To "trap" an error, you can use this code

Please Login or Register  to view this content.

I know that does not answer your question. But, if (in VB Editor) you look for Help under the IsDate function, it will give you some idea of what DateValue will accept as an argument.

I am sure this is very frustrating right now. But, it appears to me that you are tantalizingly close to having this solved.