Loop help Please

[millwalll]

Hi all,

I Need some help what I need to do it loop throught a coloum and if the cell
has a number value in it. Then compaire it with a variable I have set called
maxval. If the value of the cell is great than the variable maxval. I want
the cell value to replace the variable number then carry on testing each cell
untill the higest number is the value of the variable if this makes sense
this is kinder what I have so far....

Dim maxval As Integer ' variable for the max value
Dim startrow As Integer ' value to startrow

startrow = 0
Do While Data.Range("b2").Offset(startrow, 0).Value
startrow = startrow + 1 ' add one to the startrow to move down the coloum

Loop

any help be great thanks a lot

 

[Vergel Adriano]

you can get the max value without looping by using the Max worksheet function..

Dim maxval As Long
maxval = WorksheetFunction.Max(Sheet1.Range("A:A"))

 

[JLGWhiz]

The loop wouldn't work anyhow. The Do Until statement is incomplete.

 

[Bob Phillips]

Dim maxval As Integer ' variable for the max value
Dim startrow As Integer ' value to startrow

startrow = 0
With Data.Range("b2")
Do While .Offset(startrow, 0).Value <> ""
startrow = startrow + 1 ' add one to the startrow to move down the
coloum
If .Offset(startrow, 0).Value > maxval Then
maxval = .Offset(startrow, 0).Value
End If
Loop
End With


but it is simpler with

With Data
Set rng = .Range(.Range("B2"), .Range("B2").End(xlDown))
maxval = Application.MaxChange(rng)
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[millwalll]

The problem I got is i have a coloum mixed with numbers and text and numbers
for example
10
50
jamie60
paul70

I need to find the max value not includin the text and numbers I dont want
it to even look at them. Once it finds the max value I then need to offset
it to coloum a and disply that value in message box what I guess be somthing
like this
MsgBox .Offset(0, -1).Value

 

[Vergel Adriano]

That shouldn't be a problem for Max() because it ignores non-numeric values
(see help description for the Max function).

try:

Dim maxval As Long
maxval = WorksheetFunction.Max(Sheet1.Range("A:A"))
MsgBox maxval



or even just use one line if you don't really need the variable:

MsgBox WorksheetFunction.Max(Sheet1.Range("A:A"))

 

[millwalll]

When I try that I get run time error object required

 

[Vergel Adriano]

sorry, I didn't exactly use the same object names that you did. give this a
try. it will find the max value in column B and pop a messagebox that shows
the value that's in column A.

Dim maxval As Long
Dim maxvalIndex As Long

With WorksheetFunction
maxval = .Max(Data.Range("B:B"))
maxvalIndex = .Match(maxval, Data.Range("B:B"), False)
End With
MsgBox Data.Range("A" & maxvalIndex)