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

 

[Joe Was]

This is how to code the function in a macro:

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

But, I do not know what you really want, I did not understand your note?

Do you want the macro in one WorkBook, that opens another WorkBook and go's to a Sheet in that newly opened WorkBook and does what with your function? JSW

 

[ROBINSYN]

Macro is in 1 Workbook. I have another macro that copies sheet 1 and creates a new workbook from the copied sheet. once that happens I want to save and close new workbook created,then return to 1st workbook then perform function.
This message was edited by ROBINSYN on 2002-10-27 01:02

 

[ROBINSYN]

Would it work like this? Or do I need to use worksheetname too?

Sub ponumber()

If max Then
Cells("K8") = (max("Z:Z") + 1)
End If
End Sub

 

[ROBINSYN]

if adding functions to macro is not possible. Is there a way I could have the macro run the function in a certain cell after sheet activation?

 

[ROBINSYN]

Joe. Can the above function work?

 

[ROBINSYN]

You can email me too if you have an example in workbook.
SynDee@sympatico.ca

 

[Ivan F Moala]

Have a look in the VBA Editor
Lookup Application.Worksheetfunction

This is how you would need to code it in VBA

 

[ROBINSYN]

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)

 

[ROBINSYN]

Please help! I don't understand how this increases the value of cell"K8" based on Column "Z" . Both column and cell are located on Worksheet"Purchase Order"

ActiveCell.FormulaR1C1 = "=MAX(C[25])+1"
This message was edited by ROBINSYN on 2002-10-27 22:08