Xcel VBA Finding Max Number in Column

[JarekM]

I have a column with the data formatted like this:

L-15

S-6

L-7

L-8

R-9

D-10

S-11

D-15

R-13

L-6

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

I am trying to find a code where in the next column or somewhere else, the number that is the largest would show up, but only for that specific letter, so if I was trying to find the largest number from letter L it would be L - 15, and it would show up as that in the other column. And if I were to ask for the largest number for s, it would be S- 11.

Can someone help me?

Thank you.

 

[Akuini]

I have a column with the data formatted like this:
I am trying to find a code where in the next column or somewhere else, the number that is the largest would show up, but only for that specific letter, so if I was trying to find the largest number from letter L it would be L - 15, and it would show up as that in the other column. And if I were to ask for the largest number for s, it would be S- 11.

Can someone help me?

Thank you.

Try this:
Type a letter in B1, run the macro, the result in C1.

[B][color=Royalblue]Sub[/color][/B] a1077550a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1077550-xcel-vba-finding-max-number-column.html[/color][/i]
[B][color=Royalblue]Dim[/color][/B]  i [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Long[/color][/B], m  [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B]
[B][color=Royalblue]Dim[/color][/B] x [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]String[/color][/B]
[B][color=Royalblue]Dim[/color][/B]  va [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Variant[/color][/B], ar  [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Variant[/color][/B]

va = Range([color=brown]"A1"[/color], Cells(Rows.count, [color=brown]"A"[/color]).[B][color=Royalblue]End[/color][/B](xlUp))
x = Range([color=brown]"B1"[/color])
[B][color=Royalblue]For[/color][/B]  i = [color=crimson]1[/color] [B][color=Royalblue]To[/color][/B]  UBound(va, [color=crimson]1[/color])
    ar = Split(va(i, [color=crimson]1[/color]), [color=brown]"-"[/color])
    [B][color=Royalblue]If[/color][/B] UCase(ar([color=crimson]0[/color])) = UCase(x) [B][color=Royalblue]Then[/color][/B]
         [B][color=Royalblue]If[/color][/B]  [B][color=Royalblue]CLng[/color][/B](ar([color=crimson]1[/color])) >=  m [B][color=Royalblue]Then[/color][/B] m =  [B][color=Royalblue]CLng[/color][/B](ar([color=crimson]1[/color]))
    [B][color=Royalblue]End[/color][/B] [B][color=Royalblue]If[/color][/B]
[B][color=Royalblue]Next[/color][/B]
Range([color=brown]"C1"[/color]) = m
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]Sub[/color][/B]

Excel 2007 32 bit

	A​	B​	C​
1​	header	S	11​
2​	L-15
3​	S-6
4​	L-7
5​	L-8
6​	R-9
7​	D-10
8​	S-11
9​	D-15
10​	R-13
11​	L-6

Sheet: Sheet2

 

[Fluff]

With a formula

Excel 2013/2016
	D	E
1	L-15	S
2	S-6	11
3	L-7
4	L-8
5	R-9
6	D-10
7	S-11
8	D-15
9	R-13
10	L-6
Sheet2

Cell Formulas
Range	Formula
E2	{=MAX((LEFT(D1:D10,1)=E1)*(RIGHT(D1:D10,LEN(D1:D10)-2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JarekM]

Thank you for the help, but the formula just somehow would not work for me.

 

[JarekM]

Hi, thank you very much for the quick reply, it worked really well and I also have modified it, so it works in userform.

Here is the code:

Private Sub CommandButton1_Click()
'https://www.mrexcel.com/forum/excel-questions/1077550-xcel-vba-finding-max-number-column.html
Dim i As Long, m As Long
Dim x As String
Dim va As Variant, ar As Variant

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
x = TextBox1.Text
For i = 1 To UBound(va, 1)
ar = Split(va(i, 1), "-")
If UCase(ar(0)) = UCase(x) Then
If CLng(ar(1)) >= m Then m = CLng(ar(1))
End If
Next
TextBox2.Text = TextBox1.Text & "-" & m + 1
End Sub

It works, but I wanted to ask another question. If you know by any chance, can you make it so that all the numbers that are missing in the column, like here:

l-25

l-3

l-4

l-5

l-10

l-7

l-8

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

There are numbers missing, so is there a way you can make it so not only would the largest number show up which it L-25, but as well as all the missing numbers that come between L-3 (which is the smallest number in the column) to L-25 (which is the biggest).

Thank you

 

[Akuini]

Hi, thank you very much for the quick reply, it worked really well and I also have modified it, so it works in userform.
There are numbers missing, so is there a way you can make it so not only would the largest number show up which it L-25, but as well as all the missing numbers that come between L-3 (which is the smallest number in the column) to L-25 (which is the biggest).

Thank you

Ok, try this:

[B][color=Royalblue]Sub[/color][/B] a1077550b()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1077550-xcel-vba-finding-max-number-column.html[/color][/i]
[B][color=Royalblue]Dim[/color][/B] i [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], a [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], b [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B]
[B][color=Royalblue]Dim[/color][/B] x [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]String[/color][/B], txt [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]String[/color][/B]
[B][color=Royalblue]Dim[/color][/B] va [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], ar [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B]
[B][color=Royalblue]Dim[/color][/B] d [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Object[/color][/B]

[B][color=Royalblue]Set[/color][/B] d = CreateObject([color=brown]"scripting.dictionary"[/color])
va = Range([color=brown]"A1"[/color], Cells(Rows.count, [color=brown]"A"[/color]).[B][color=Royalblue]End[/color][/B](xlUp))
x = Range([color=brown]"B1"[/color])
    
    [B][color=Royalblue]For[/color][/B] i = [color=crimson]1[/color] [B][color=Royalblue]To[/color][/B] UBound(va, [color=crimson]1[/color])
        ar = Split(va(i, [color=crimson]1[/color]), [color=brown]"-"[/color])
        [B][color=Royalblue]If[/color][/B] UCase(ar([color=crimson]0[/color])) = UCase(x) [B][color=Royalblue]Then[/color][/B]
            d.Add [B][color=Royalblue]CLng[/color][/B](ar([color=crimson]1[/color])), [color=brown]""[/color]
        [B][color=Royalblue]End[/color][/B] [B][color=Royalblue]If[/color][/B]
    [B][color=Royalblue]Next[/color][/B]

a = Application.Min(d.Keys)
b = Application.Max(d.Keys)

    [B][color=Royalblue]For[/color][/B] i = a [B][color=Royalblue]To[/color][/B] b
        [B][color=Royalblue]If[/color][/B] [B][color=Royalblue]Not[/color][/B] d.Exists(i) [B][color=Royalblue]Then[/color][/B] txt = txt & [color=brown]";"[/color] & i
    [B][color=Royalblue]Next[/color][/B]

txt = Right(txt, Len(txt) - [color=crimson]1[/color])
Range([color=brown]"C1"[/color]) = b
Range([color=brown]"D1"[/color]) = txt
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]Sub[/color][/B]

Excel 2007 32 bit

	A​	B​	C​	D​
1​	header	L	15​	8;9;11;12;13;14
2​	L-15
3​	S-6
4​	L-7
5​	L-10
6​	R-9
7​	D-10
8​	S-11
9​	D-15
10​	R-13
11​	L-6

Sheet: Sheet1

 

[JarekM]

The code works really well, but there is an error in in it. Whenever there is a duplicated number like lets say L-25 was shown up twice in the column, whenever I Run Sub/Userform an error sign would just show up. Is there a way to fix this?

Also one other questions.
So this is the code that we have modified:

Dim i As Long, a As Long, b As Long
Dim x As String, txt As String
Dim va As Variant, ar As Variant
Dim d As Object

Set d = CreateObject("scripting.dictionary")
va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
x = TextBox1.Text

For i = 1 To UBound(va, 1)
ar = Split(va(i, 1), "-")
If UCase(ar(0)) = UCase(x) Then
d.Add CLng(ar(1)), ""
End If
Next

a = 1
b = Application.Max(d.keys)

For i = a To b
If Not d.Exists(i) Then txt = txt & ";" & i
Next

txt = Right(txt, Len(txt) - 1)
TextBox2.Text = TextBox1.Text & "-" & b + 1
TextBox3.Text = txt <----------------------
End Sub

I have changed it so instead of the number sequence showing up on the spread sheet the sequence would show up in a text box like the arrow shows, can you make it so that it shows up in a list box in userform, if so that would be very helpful?

Thank you so much again.

 

[Akuini]

The code works really well, but there is an error in in it. Whenever there is a duplicated number like lets say L-25 was shown up twice in the column, whenever I Run Sub/Userform an error sign would just show up. Is there a way to fix this?


I have changed it so instead of the number sequence showing up on the spread sheet the sequence would show up in a text box like the arrow shows, can you make it so that it shows up in a list box in userform, if so that would be very helpful?

Thank you so much again.

Ok, try this:

[B][COLOR=Royalblue]Private[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B] CommandButton1_Click()
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], a [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], b [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] x [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B], txt [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], ar [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] d [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Object[/COLOR][/B]

[B][COLOR=Royalblue]Set[/COLOR][/B] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
va = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.Count, [COLOR=brown]"A"[/COLOR]).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp))
[I][COLOR=seagreen]'x = Range("B1")[/COLOR][/I]
x = TextBox1.Text
    [B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
        ar = Split(va(i, [COLOR=crimson]1[/COLOR]), [COLOR=brown]"-"[/COLOR])
        [B][COLOR=Royalblue]If[/COLOR][/B] UCase(ar([COLOR=crimson]0[/COLOR])) = UCase(x) [B][COLOR=Royalblue]Then[/COLOR][/B]
           d([B][COLOR=Royalblue]CLng[/COLOR][/B](ar([COLOR=crimson]1[/COLOR]))) = [COLOR=brown]""[/COLOR]
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]

a = [COLOR=crimson]1[/COLOR]  [I][COLOR=seagreen]'Application.Min(d.Keys)[/COLOR][/I]
b = Application.Max(d.Keys)

    [B][COLOR=Royalblue]For[/COLOR][/B] i = a [B][COLOR=Royalblue]To[/COLOR][/B] b
        [B][COLOR=Royalblue]If[/COLOR][/B] [B][COLOR=Royalblue]Not[/COLOR][/B] d.Exists(i) [B][COLOR=Royalblue]Then[/COLOR][/B] txt = txt & [COLOR=brown]";"[/COLOR] & i
    [B][COLOR=Royalblue]Next[/COLOR][/B]

txt = Right(txt, Len(txt) - [COLOR=crimson]1[/COLOR])
TextBox2.Text = TextBox1.Text & [COLOR=brown]"-"[/COLOR] & b + [COLOR=crimson]1[/COLOR]
[I][COLOR=seagreen]'TextBox3.text = txt[/COLOR][/I]
ListBox1.List = Split(txt, [COLOR=brown]";"[/COLOR])
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

 

[JarekM]

Thank you so much...it works greatly and exactly how I envisioned it.

But one last question, if you're willing to help. I need one more code, it is an if condition, for example:

If textbox1.value "L" the do something
but the problem that I have is that the numbers in the textbox are changing always from 1 to beyond and the letter is the same, but sometimes there are two (the letters and numbers are the same as the reply's above). Is there a way to have a universal code.

Than you in advance.

 

[Akuini]

If textbox1.value "L" the do something
but the problem that I have is that the numbers in the textbox are changing always from 1 to beyond and the letter is the same, but sometimes there are two (the letters and numbers are the same as the reply's above). Is there a way to have a universal code.

I don't understand

that the numbers in the textbox are changing

in which textbox?

but sometimes there are two (the letters and numbers are the same as the reply's above)

2 letters where?

Can you explain the process of what you do step by step?