find the highest number in a string of numbers

[Jerk24]

I have asked two questions today both answered. sooooo

is it possible to find the highest number is a string of numbers, in different cells:

cell c3 = 12+13+14
cell d3 = 20+21+22
cell e3 = 12+20+20

there are alot more and was wondering if there was a way for me to just add everything and not worry about the "HIGH Game"? the formula i have =LARGE(C3:K3,1) Which brings the highest number, not the single highest number

<tbody>
</tbody><colgroup><col></colgroup>

 

[Jerk24]

my spreadsheet contains 47 other sets of the data. so the message 7 is just a snap shot if the top of the sheet.

sorry I did not specify that.

 

[Rick Rothstein]

my spreadsheet contains 47 other sets of the data. so the message 7 is just a snap shot if the top of the sheet.

But where are they located at? I presume they are spaced evenly down the sheet, so if you tell me the cell address that "Wk 1" is in for the first 3 sets of data (the first one is apparently in C2, so where are the others located at), I should be able to work out the rest on my own.

 

[hiker95]

Jerk24,

New sample raw data:

Excel 2007
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Wk 1	Wk 2	Wk 3	Wk 4	Wk 5	Wk 6	Wk 7	Wk 8	Wk 9
3	43	17	18	36
4	Wk 10	Wk 11	Wk 12	Wk 13	Wk 14	Wk 15	Wk 16	Wk 17	Wk 18	# Games	Points	AVG	Hi Gm	Hi Trp	Turks	Lst Wk
5										12	124	10.33		43	0	36	10
6
Sheet1

Cell Formulas
Range	Formula
C3	= 12+13+14+4
D3	=10+2+5
E3	=1+8+9
F3	=21+5+10

After the updated macro:

Excel 2007
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Wk 1	Wk 2	Wk 3	Wk 4	Wk 5	Wk 6	Wk 7	Wk 8	Wk 9
3	43	17	18	36
4	Wk 10	Wk 11	Wk 12	Wk 13	Wk 14	Wk 15	Wk 16	Wk 17	Wk 18	# Games	Points	AVG	Hi Gm	Hi Trp	Turks	Lst Wk
5										12	124	10.33	21	43	0	36	10
6
Sheet1

Cell Formulas
Range	Formula
C3	= 12+13+14+4
D3	=10+2+5
E3	=1+8+9
F3	=21+5+10

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub GetHighestNumberV2()
' hiker95, 11/03/2013
' http://www.mrexcel.com/forum/excel-questions/736816-find-highest-number-string-numbers.html
Dim c As Range, rng As Range, h As String, s, i As Long, maxn As Long
Range("O5").ClearContents
maxn = 0
For Each c In Range("C3:K3")
  If c <> "" Then
    h = c.Formula
    s = Split(Right(h, Len(h) - 1), "+")
    For i = LBound(s) To UBound(s)
      If s(i) > maxn Then maxn = s(i)
    Next i
  End If
Next c
For Each c In Range("C5:K5")
  If c <> "" Then
    h = c.Formula
    s = Split(Right(h, Len(h) - 1), "+")
    For i = LBound(s) To UBound(s)
      If s(i) > maxn Then maxn = s(i)
    Next i
  End If
Next c
Range("O5") = maxn
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetHighestNumberV2 macro.

 

[hiker95]

Jerk24,

my spreadsheet contains 47 other sets of the data. so the message 7 is just a snap shot if the top of the sheet.

sorry I did not specify that.

In the future you should include all the ranges where the formula are located, and, what cell will be receiving the final result.

This way we can find a solution the first time.


What are the remaining ranges that will contain the formulae?

 

[Jerk24]

Hiker95,

you are correct. WK1 is in cell C2, data C3, WK10 is in C5
then starts a new name, so C5:K5,C7:K7, C9:K9 ect all contain the data needed. then O(5,9,13,17) are where the answer go.

sorry to be so difficult on this.



The data ends on C193:k193, and the final anwser will end on O193.

I was looking at a Index or a indirect. but none seem to work.

 

[hiker95]

Jerk24,

my spreadsheet contains 47 other sets of the data.

I do not want to try and figure out where each set of two rows of formulae, and, the cell where the results will go for that set.

One last try:

Please list them out (continue the below list for all SETS):
C3:K3, C5:K5, to O5
C7:K7, K9:K9, to O9


If you can not supply an accurate list, then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Rick Rothstein]

you are correct. WK1 is in cell C2, data C3, WK10 is in C5
then starts a new name, so C5:K5,C7:K7, C9:K9 ect all contain the data needed. then O(5,9,13,17) are where the answer go.

The data ends on C193:k193, and the final anwser will end on O193.

You said you wanted this to be automatic (which I presume means you want the maximum in Column O to update whenever any of the values in the Wk# cells change. Here is event code (different from a macro, so it installs in a different location... see below) which will do that...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim X As Long, Pluses As String, Numbers As Variant, Rng As Range
  Const SetOffsets As Long = 4
  For X = 0 To 47
    If Not Intersect(Target, Range("C3:K3,C5:K5").Offset(X * SetOffsets)) Is Nothing Then
      Set Rng = Range("C3:K3,C5:K5").Offset(X * SetOffsets)
      GoTo Continue
    End If
  Next
  Exit Sub
Continue:
  With WorksheetFunction
    Pluses = .Trim(Join(.Index(Rng.Areas(1).Formula, 1, 0), " ")) & _
             "+" & .Trim(Join(.Index(Rng.Areas(2).Formula, 1, 0), " "))
    Pluses = .Trim(Replace(Replace(Pluses, "+", " "), "=", " "))
    Cells(Rng(1).Row + 2, "O").Value = Evaluate("MAX(" & Replace(Pluses, " ", ",") & ")")
  End With
End Sub

NOTE: The above event code only does its work for the set where the value has changed in a Wk# cells. I mention this because if your existing 47 or 48 maximum values in Column O are not currently up-to-date, then this code will not go back and update them unless you change something in one of the Wk# cells for the set... the code is dynamic and only works on a "per set" basis as that set is changed.


HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[Jerk24]

thank you. that seems to work great. Have a good rest of the weekend.

 

[Jerk24]

Good afternoon Rick,

The code worked great and unfortunately my while transferring files, i lost all of my score sheets. while i had a back up still quite a pain.

anyhow after putting everything back, the code seems to not like that there is a ABS in cells, it is returning #Name error. I cannot have a 0 since it will affect other rows. it worked before (i think at least) any ideas?

 

[Rick Rothstein]

anyhow after putting everything back, the code seems to not like that there is a ABS in cells, it is returning #Name error. I cannot have a 0 since it will affect other rows. it worked before (i think at least) any ideas?

Where did the ABS come from (there is no "ABS" anywhere in my code)? What exactly is in the cells along with that ABS (please post the entire cell's value)?