Max value in 1 column when all values format are text

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

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


"coco" wrote:

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

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


coco wrote:
>
> 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

--

Dave Peterson