Hello,

I have a monitoring sheet which consists of a sequence of numbers, at the
moment I have used a =MAX() formula to display the maximum # on the status
bar, this works simple as I have defined the range. Now I want it to be more
dynamic; where I shouldn't be giving the range in fact there should be
iteration on the particular cell and there it should determine the maximum #
and add 1 to it.

Can anyone tell me how to use it and how to use such formulas in VBA

E.g.

Total Tasks = 4 (assuming this is the highest # so far)

A
--------------
1
2
3
4


I should rather iterate instead of using a defined range.


TIA

Regards

Mumshad

Not sure what you really need but have you tried =MAX(A:A) ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Sincere" wrote in message
...
Hello,

I have a monitoring sheet which consists of a sequence of numbers, at the
moment I have used a =MAX() formula to display the maximum # on the status
bar, this works simple as I have defined the range. Now I want it to be
more
dynamic; where I shouldn't be giving the range in fact there should be
iteration on the particular cell and there it should determine the maximum
#
and add 1 to it.

Can anyone tell me how to use it and how to use such formulas in VBA

E.g.

Total Tasks = 4 (assuming this is the highest # so far)

A
--------------
1
2
3
4


I should rather iterate instead of using a defined range.


TIA

Regards

Mumshad

I think you need to define your range dynamically something like this:
Dim iLastRow As Integer

iLastRow = Range("B65536").End(xlUp).Row

Myrange = Range("B1:B" & iLastRow).Address

Worksheets("Sheet1").Range("C1").Value = "=Max(" & Myrange & ")"

End Sub
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Sincere" wrote:

Hello,

I have a monitoring sheet which consists of a sequence of numbers, at the
moment I have used a =MAX() formula to display the maximum # on the status
bar, this works simple as I have defined the range. Now I want it to be more
dynamic; where I shouldn't be giving the range in fact there should be
iteration on the particular cell and there it should determine the maximum #
and add 1 to it.

Can anyone tell me how to use it and how to use such formulas in VBA

E.g.

Total Tasks = 4 (assuming this is the highest # so far)

A
--------------
1
2
3
4


I should rather iterate instead of using a defined range.


TIA

Regards

Mumshad

Michael, the Yes button is not visible when you read groups with Outlook
Express (and many other newsgroup readers)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Michael" wrote in message
...
I think you need to define your range dynamically something like this:
Dim iLastRow As Integer

iLastRow = Range("B65536").End(xlUp).Row

Myrange = Range("B1:B" & iLastRow).Address

Worksheets("Sheet1").Range("C1").Value = "=Max(" & Myrange & ")"

End Sub
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Sincere" wrote:

Hello,

I have a monitoring sheet which consists of a sequence of numbers, at the
moment I have used a =MAX() formula to display the maximum # on the
status
bar, this works simple as I have defined the range. Now I want it to be
more
dynamic; where I shouldn't be giving the range in fact there should be
iteration on the particular cell and there it should determine the
maximum #
and add 1 to it.

Can anyone tell me how to use it and how to use such formulas in VBA

E.g.

Total Tasks = 4 (assuming this is the highest # so far)

A
--------------
1
2
3
4


I should rather iterate instead of using a defined range.


TIA

Regards

Mumshad

Hello,

I did tried something which helped me get my work done; below is the code
for reveiw.

Sub Iterate()


Dim r As Integer

r = 1



Me.Range("C6").Activate



Do While 1 = 1

If Me.Range("C6").Cells(r, 1).Value < 0 Then

Debug.Print Me.Range("c6").Cells(r, 1).Value

Else

Debug.Print "Null"

End If

If Me.Range("C6").Cells(r, 1).Value = 0 Then

Me.Range("E4").Value = Me.Range("C6").Cells(r - 1, 1).Value + 1

Debug.Print Range("E4").Value

MsgBox "No more numbers"

r = 0

Exit Sub

End If

r = r + 1

Loop

***** Rem Me.Range("E4").Value = Me.Range("C6").Cells(r - 1, 1).Value +
1
Example
-----------
1
2
3
* when the loop comes here; it found no value, therefore i used the r-1 to
get the last max. value

End Sub


Thanks Indeed, I would appreciate if I can be with more advises or more
resources.

"Sincere" wrote:

Hello,

I have a monitoring sheet which consists of a sequence of numbers, at the
moment I have used a =MAX() formula to display the maximum # on the status
bar, this works simple as I have defined the range. Now I want it to be more
dynamic; where I shouldn't be giving the range in fact there should be
iteration on the particular cell and there it should determine the maximum #
and add 1 to it.

Can anyone tell me how to use it and how to use such formulas in VBA

E.g.

Total Tasks = 4 (assuming this is the highest # so far)

A
--------------
1
2
3
4


I should rather iterate instead of using a defined range.


TIA

Regards

Mumshad