Solved: searching for a largest value

[ming]

How to get the largest value (=120) in Col.A ? Thanks!


Column.A
100, 104, 99
110, 89, 70
111, 120, 103
10
5, 4, 9

[MaximS]

use function:


=MAX(A2:A50)

[ming]

No, the result of your formula is 10. But I want to get 120.

[GTO]

Greetings ming,

I believe MaximS' should work. Are you sure you aren't showing values in columns A, B, and C?

Please state the range we're looking to find the MAX value in.

Thanks and hope to help,

Mark

[ming]

Pls see attached file, I type the formula in cell B1, the result is become 10 not 120. Pls help! Thanks!

[GTO]

Okay - I have to shut down, but wanted to ask if there's any chance of placing the numbers in seperate cells?

Mark

[ming]

No, the original file is not my own, I don't want to reinput the data again, because there are many many rows.....
Thanks!

[GTO]

When you say "...the original file is not my own...", does this mean you are extracting your data from another workbook/text file/other?

If so, from where (workbook, text file, etc), and how (manually or thru code you're not showing)?

Mark

[Krishna Kumar]

Hi,

Using a helper column,

Select A1,

Hit Ctrl+F3,

Define Str

Refers to : =EVAL("{"&SUBSTITUTE(Sheet2!$A1,", ",";")&"}")

Add > OK

Now in B1 and copied down,
=MAX(Str)

In C1,

=MAX(B1:B5)

H

[ming]

Thanks H, I tried your method, but there's an error (#Name?) in B1. I don't what is the problem.

Hi Mark, Pls see attached file, my client send me this file daily, then I need to know the largest number in Column J. Thanks!

[GTO]

Hi Ming,

The workbook is attached. The only notes I would add at this juncture are that the source workbook "PackingList.xls" must be open when 'SeperateAndFindMax' is run.

Hope this helps,

Mark


Option Explicit
Sub SeperateAndFindMax()
Dim _
wbSource As Workbook, _
wksSource As Worksheet, _
rngRange As Range, _
rCell As Range, _
lngRowsCnt As Long

'// Set source workbook. //
Set wbSource = Workbooks("PackingList.xls")
'// ... source worksheet //
Set wksSource = wbSource.Worksheets("Packing List")

'// Set range to the single cell where we found the last value. //
Set rngRange = wksSource.Range("J65536").End(xlUp)

'// Record what row this was, minus 6, so we know where to end the range //
'// (row-wise) in your workbook. //
lngRowsCnt = rngRange.Row - 6

'// Re-use the variable, now setting a range to be inclusive of all the //
'// cells that may have a value in them. //
Set rngRange = wksSource.Range("J7", rngRange)

'// Specify by worksheet (tab) name or codename //
With ThisWorkbook.Worksheets("Sheet1")
'// To eliminate Excel from guessing at number vs. text on the way in, //
'// we'll take from PackingList as text, and coerce it back to good //
'// numbers in columns B:K. //
.Columns("A:A").NumberFormat = "@"
.Columns("B:K").NumberFormat = "0"

'// Ensure no old values //
.Range("A:K").ClearContents

'// Snatch all the values from PackingList. //
.Range(Cells(1, 1), Cells(lngRowsCnt, 1)) = rngRange.Value

'// Re-use variable, now setting our range to the range of cells in //
'// Column A of this workbook. //
Set rngRange = .Range(.Cells(1, 1), .Cells(lngRowsCnt, 1))

'// For ea cell in this range... //
For Each rCell In rngRange
'// If the cell isn't empty (the blank cells will otherwise toss //
'// the code into a tizzy when it attempts to parse what is not //
'// there. //
If Not rCell.Value = Empty Then

'// We found a value, so parse ea such cell in Column A, into //
'// the cell(s) to the right. By leaving out the .FieldInfo:= //
'// arg, we can let Excel guess at to how many columns are //
'// needed. //
rCell.TextToColumns Destination:=rCell.Offset(0, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
TrailingMinusNumbers:=True
End If
Next

'// Now we'll set the range from Row 1, Column B To Row (whatever we //
'// found to be the highest needed numerical row), Column K. Column K //
'// we subjectively chose, so change to suit. //
Set rngRange = .Range(.Cells(1, 2), .Cells(lngRowsCnt, 11))

End With

'// Change to whatever you wanted to do with the highest val. //
MsgBox Application.WorksheetFunction.Max(rngRange)

End Sub

[ming]

Thanks Marks, I tried to run the code, but stop in here:
--> TrailingMinusNumbers:=True
and the error message showing " Compile error : Named agument not found"
Please help!!

[GTO]

Hi Ming,

What version of Excel are you using? The above was written in 2003. If you are in 2000, then this may be an added argument.

I would suggest before deleting too much stuff, in the code window, select .TextToColumns and press the F1 key. This should take you to the vba Help topic for TextToColumns. There you can see if TrailingMinusNumbers is listed.

If its not, then delete this argument.

Let me know what version you are using.

Mark

[Krishna Kumar]

Hi,

Another approach.

Sub kTest()
Dim a, i As Long, x, MaxValue, c As Long
a = Range("j7", Range("j" & Rows.Count).End(xlUp))
For i = 1 To UBound(a, 1)
x = Split(Application.Index(a, i, 1), ",")
For c = 0 To UBound(x)
MaxValue = Application.Max(MaxValue, x(c))
Next
Next
MsgBox MaxValue
End Sub

HTH

[ming]

Hi HTH,
That worked!

Thanks Mark, HTH,
Thank you so much for help and appreciate taking the time to answer my questions! :bow:

[GTO]

Hi ming,

Just out of curiousity, do you have Excel2000?

Regardless, Krishna's is way nicer:clap: :bow:

Mark