excel VBA

[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

 

[Bob Phillips]

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

--

HTH

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

 

[JE McGimpsey]

One way:

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

 

[Ian]

Do you really want to do it with code?
If not, you don't need to check for data before checking for the maximum.
=MAX(A:A) will display the maximum value in column A. Alternatively
=MAX(A1:A100) just checks the first 100 rows.

 

[Harlan Grove]

JE McGimpsey wrote...

One way:

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

....

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.

 

[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!

 

[magix]

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.

[/QUOTE]


Thanks to all of you.