Max value in 1 column when all values format are text

[Guest]

I have an error when I run this function
"Application.WorksheetFunction.Max(rng)"
because the information in C:C is in TEXT format.

I mean, For example:
In this case "rng" has only integer-numbers in a text format.
Dim rng As Range
Dim MaxUnit2 As Integer
Dim MinUnit2 As Integer

Set rng = Worksheets("Sheet1").Range("C:C")
MaxUnit2 = Application.WorksheetFunction.Max(rng)
MinUnit2 = Application.WorksheetFunction.Min(rng)

Can I use the same function : "Application.WorksheetFunction.Max(...)" TYPE
for this particular case?

Thanks

Coco

 

[Guest]

Does VBA have the equivalent of the MAXA worksheet function? MAXA does not
get upset by mixing numbers with text.

 

[Dave Peterson]

If I were doing it in a cell, I'd use this formula:

=MAX(IF(ISNUMBER(-C1:C100),--C1:C100))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(and you can't use the whole column.)

In code, that same formula could be evaluated like:

Option Explicit
Sub testme()

Dim myRng As Range

With ActiveSheet
Set myRng = .Range("c1", .Cells(.Rows.Count, "C").End(xlUp))

MsgBox .Evaluate("=MAX(IF(ISNUMBER(-" & myRng.Address(external:=True) _
& "),--" & myRng.Address(external:=True) & "))")

End With

End Sub