SOLVED! Function as Macro

[ROBINSYN]

Can I create a macro for a function.

=MAX(Z:Z)+1

The macro needs to select workbook"New Purchase Order" Sheetname"Purchase Order"

Then perform the function.

Can this be done?
This message was edited by ROBINSYN on 2002-10-27 16:44
This message was edited by ROBINSYN on 2002-10-27 22:05
This message was edited by ROBINSYN on 2002-10-28 07:21

 

[Ivan F Moala]

On 2002-10-27 17:32, ROBINSYN wrote:
Trying but can't seem to get the handle on this.

Result is suppose to go in cell K8

Set myrange = Worksheets("Purchase Order").Range("k8")
Set WorksheetFunction = xlMax
Application.WorksheetFunction.Max (("Z:Z") + 1)

Should look something like this;<pre/>
Sub Tester()
Dim MyRange As Range
Dim MyVal

'// Define the range to get Max here
Set MyRange = Worksheets("Purchase Order").Range("Z:Z")

MyVal = Application.WorksheetFunction.Max(MyRange)
MsgBox "The max value @ " & MyRange.Address & " is " & MyVal

Range("K8") = MyVal+1

End Sub</pre>


_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails

This message was edited by Ivan F Moala on 2002-10-27 22:44

 

[Joe Was]

Robinsyn,

In row-column notation Column "Z" is the 25th column!

This code: ActiveCell.FormulaR1C1 = "=MAX(C[25])+1"

Adds your formula to the active cell when run: find the max value of Column Z the 25th column and add one to it. The code below does not add a formula to your cell it adds the value of the formula to your cell only.


This will run from a standard Module.
It will search column Z for the largest value and add one to that value. Then that value is inserted into cell K8 as a value not a formula. Hope this helps. JSW

P.S. Sorry I was out.

Sub MyMax()
Dim MaxVal As Long
Worksheets("Purchase_Order").Select
MyRng = Worksheets("Purchase_Order").Range("Z:Z")
MaxVal = Application.WorksheetFunction.Max(MyRng) + 1
Worksheets("Purchase_Order").Range("K8").Value = MaxVal
End Sub
This message was edited by Joe Was on 2002-10-28 00:40

 

[ROBINSYN]

Thanks for all your help and patience!
Works great!