Application.WorksheetFunction.Max Against Column Containing Formulas Question:

[MattDW]

MrExcel forum users,

I am using the below VBA formula in an attempt to find the largest value of a cell range J3:J42.

However, the data in the J3:J42 range of cells has been created using a MID formula that feeds from the adjacent column that contains a string of text and numbers(the MID formula results in only the number portion being displayed in cells J3:J42).

I know the Application.WorksheetFunction.Max works when used against a column containing numerals. Is there a way to adjust this line to work correctly against a column containing formulas by reading the value of each cell?

Sub LargestValue()
'Finds Largest Number In The "Netbook Friendly Number - Number Only" Column Of The Netbook Number Worksheets
Dim WS
Call DefineRanges
WS = ActiveSheet.Name
MaxNum = Application.WorksheetFunction.Max(Worksheets(WS).Range(NetbookCartWSSortByCol))
End Sub

Thanks much!

-Matt-

 

[njimack]

Can you not coerce the text results of the MID function to numerical? e.g
=MID(A1,1,1)+0
=--MID(A1,1,1)

 

[MattDW]

Can you not coerce the text results of the MID function to numerical? e.g
=MID(A1,1,1)+0
=--MID(A1,1,1)

Interesting idea Njimack,

The formula I used in column J is:

=IF(I3="","",IF(ISERROR(FIND("-",I3)),SUMPRODUCT(MID(0&I3,LARGE(INDEX(ISNUMBER(--MID(I3,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),MID(I3,SEARCH("-",I3)+1,LEN(I3))))

This is because the value in column I contains a combination of straight numerals, a word with a dash followed by a numeral and a letter numeral combination. Any ideas how I might coerce the results of the above formula into a numeral?

Thanks much,

-Matt-

 

[MattDW]

Can anybody tell me if there's a way to convert MaxNum = Application.WorksheetFunction.Max(Worksheets(WS).Range(NetbookCartWSSortByCol)) so that it's evaluating the value of the cell that is returned by the formula, rather than looking at the cell as a non-numeric value and returning a 0?

Thanks much,

-Matt-