excel VBA

M

magix

Hi,

I would like to achieve two things in my excel vbscript.
1. check if there is any value in any cell in a column
2. If there is/are value(s), find the Highest value.

Any helps ?

Magix
 
B

Bob Phillips

=IF(COUNT(A:A)>0,MAX(A:A),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

JE McGimpsey

One way:

Dim result As Double
If Application.Count(Columns(1)) Then _
result = Application.Max(Columns(1))
 
H

Harlan Grove

JE McGimpsey wrote...
One way:

Dim result As Double
If Application.Count(Columns(1)) Then _
result = Application.Max(Columns(1))
Click to expand...
....

If you're not storing the result of the .Count call, why bother calling
it? result is intitialized to zero, so if there were no numbers in col
1 (A), it'd remain zero. However, the .Max call would assign zero to
result if there were no numbers in col 1 (A). Either way (with or
without the If check), result would be zero.

Better either to store the return value from the .Count call in another
variable or define result as a Variant, which would be initialized to
Empty rather than zero.
 
J

JE McGimpsey

Yup - I shortened it from

Dim result As Double
If Application.Count(Columns(1)) Then
result = Application.Max(Columns(1))
Else
'do something else if no values in column 1
End If

to make it less confusing. Of course the change made it more so.

Thanks for the correction, Harlan!
 
M

magix

JE McGimpsey said:
Yup - I shortened it from

Dim result As Double
If Application.Count(Columns(1)) Then
result = Application.Max(Columns(1))
Else
'do something else if no values in column 1
End If

to make it less confusing. Of course the change made it more so.

Thanks for the correction, Harlan!


...

If you're not storing the result of the .Count call, why bother calling
it? result is intitialized to zero, so if there were no numbers in col
1 (A), it'd remain zero. However, the .Max call would assign zero to
result if there were no numbers in col 1 (A). Either way (with or
without the If check), result would be zero.

Better either to store the return value from the .Count call in another
variable or define result as a Variant, which would be initialized to
Empty rather than zero.
Click to expand...
[/QUOTE]


Thanks to all of you.
 
Top