working with worksheest

[blueflash]

I am trying to do something pretty simple but don't seem to be able to get it right.
I want to put the maximum value of data from a column in one sheet into a cell on another sheet.
Here is the code I am using:

Sub datamax()

Dim data_range As Range

Sheets("Sheet1").Select
    With Sheets("sheet1")
        Set data_range = .Range(.Cells(1, "a"), .Cells(54, "a"))
    End With
Sheets("Sheet2").Select
    Range("A1").Select
    ActiveCell.Value = "=max(data_range)"

End Sub

Could someone put me straight please?
Thanks

 

[Firefly2012]

Hi

You don't need all those Selects in there and are you after just the max value or a formula to show the max value? Assuming the latter, try changing your last statement to:

ActiveCell.Formula = "=MAX(" & data_range.Address & ")"

 

[blueflash]

Thanks for quick reply Firefly.
Actually, the end result of what I want is the value of ((max of values in sheet1-min of values in sheet1)/50)

 

[Firefly2012]

Well, you could just return the value:

ActiveCell.Value = (Application.Max(data_range) - Application.Min(data_range))/50

or you could build that into your formula (probably the approach I would prefer just because you havea clear audit trail):

ActiveCell.Formula = "=(MAX(" & data_range.Address & ") - MIN(" & data_range.Address & "))/50"

 

[blueflash]

Thanks Firefly.
The first option writes the correct values ok but the second option just writes the formula into the cell (and there is no reference to the data range in Sheet1). I would prefer to use the second option because as you say it leaves a better audit trail but it doesn't write the actual value I need to use.

Well, you could just return the value:

ActiveCell.Value = (Application.Max(data_range) - Application.Min(data_range))/50

or you could build that into your formula (probably the approach I would prefer just because you havea clear audit trail):

ActiveCell.Formula = "=(MAX(" & data_range.Address & ") - MIN(" & data_range.Address & "))/50"

 

[Firefly2012]

Apologies - I neglected to account for the range being on another sheet to where the formula resides. It should be this:

ActiveCell.Formula = "=(MAX('" & data_range.Parent.Name & "'!" & data_range.Address & ") - MIN('" & data_range.Parent.Name & "'!" & data_range.Address & "))/50"

Be careful when you copy the above - there are apostrophes leading/trailing double quotes and thus easy to miss

 

[blueflash]

Thanks for your help but I think I am going to have to look for another way round this.
I copied your code exactly as is into my program and then set the data range

    Sheets("data").Select    Set data_range = Cells(2, 1).Cells(19, 1)

and ran it, the result was that in the active cell on the "results" sheet I finished with

=(MAX(Data!$A$20) - MIN(Data!$A$20))/50

when, with the data in my range, the value should have been 0.4

 

[Firefly2012]

You have amended how you are assigning a reference to data_range (compared to your first post) so you end up with only a single cell - do you perhaps mean this instead:

Set data_range = Range(Cells(2, 1),Cells(19, 1))

 

[blueflash]

Thanks for that, I missed the typo of . for ,

Fixing the typo gave me the right result.
I also found that

ActiveCell.Value = (Application.Max(data_range) - Application.Min(data_range)) / 50

gave the right result

There was another mistake in the original code

[COLOR=#574123]ActiveCell.Formula = "=(MAX(" & data_range.Address & ") - MIN(" & data_range.Address & "))/50"[/COLOR]

it should have been

[COLOR=#574123]ActiveCell.Formula = "=(MAX(" & data_range.Address & ") - MIN(" & data_range.Address & ")/50)"[/COLOR]

So all is now ok. Thanks for your patience Firefly