Help with MAX code

[Corey]

Can anyone help me with this code:

Sub Macro10()
' Macro recorded 7/07/2006 by Corey
Range("R59").Select
ActiveCell.FormulaR1C1 = "=MAX('1:" & _
Worksheets(Worksheets.Count).Name & _
"'!R[-8]C[-10])" ' <============= I dont understand this bit here.... What
it does
Range("J59").Select

End Sub

When i place a value in Cell R59, then click a button to run the macro, the
value dissapears and the cell J59 is selected but nothing pasted there.

I want the MAX value of ALL sheets to be displayed in cell R59 regardless of
what worksheet is being viewed.

And this value (plus 1)placed in cell J59, if i run the macro.


EG.


R59 = 100150

Click button to run macro10

Copies R59 adds 1(100151)

Pastes it into J59

If the MAX value cannot be displayed unless a macro is run i don't mind, so
long as the final result is the MAX value+1=J59


Any help appreciated.

Corey

 

[Leith Ross]

Hello Corey,

Maybe this will help. This is what your macro is doing...

1. Select cell R59 on the active sheet.
2. Set R59's formula = MAX("1:" & "last worksheet's name!" & H51's
value)
3. Select J59 on the active sheet.

When using the MAX worksheet function, if an argument is an array or
reference, only numbers in that array or reference are used. Empty
cells, logical values, or text in the array or reference are ignored.
You don't indicate if your checking a range of cells or an array on the
worksheet, so I can only guess what you're after. Hope this helps.

Sincerely,
Leith Ross

 

[Corey]

OK,
Almost there now.

The code I have now that finds the MAX value of ALL sheets and places it in
Cell R59.

Sub Macro10()
' Macro recorded 7/07/2006 by Corey
Range("R59").Select

ActiveCell.FormulaR1C1 = "=MAX('Enter-Exit Page:" & _
Worksheets(Worksheets.Count).Name & _
"'!R[0]C[-8])"


End Sub

The ONLY thing i want to modify now is:

HOW DO I ADD (1) to this value found and displayed in Cell R59?

I tried :
ActiveCell.FormulaR1C1 = "=MAX('Enter-Exit Page:" & _
Worksheets(Worksheets.Count).Name & _
"'!R[0]C[-8])" +1


But get an error.

???

Corey....