Xcel VBA Finding Max Number in Column

[JarekM]

I have a column with the data formatted like this:

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl64, width: 64"]L-15
[/TD]

[TD="class: xl65"]S-6[/TD]

[TD="class: xl64"]L-7
[/TD]

[TD="class: xl64"]L-8
[/TD]

[TD="class: xl66"]R-9
[/TD]

[TD="class: xl67"]D-10
[/TD]

[TD="class: xl65"]S-11[/TD]

[TD="class: xl67"]D-15
[/TD]

[TD="class: xl66"]R-13[/TD]

[TD="class: xl64"]L-6


[/TD]

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

 

[JarekM]

Sorry I am a beginner at this, so the id numbers consists of the letter in the beginning and then the number, for example:
L-24, S-45, N-65, L-65, S-2, etc...
and the numbers will populate the combobox, so when I click on some number in the combobox then I want the formula to do something and for this part I have a code.


The problem I have is that I just don't know how to make it work correctly for the if statement. For example:

If combobox1.value = "L-1" then (The formula should do something based on the base letter in the id number).

In this example the base letter is L and I want the code to run on this base letter and not on the number because it will be changing.
So if the id number (L-22 etc...) will start with the letter L, then it print the invoice that I have created, for these parts I have a code. If the id number starts with a different number then it wouldn't change anything.

Sorry again, I do not know the technical words for VBA, so you will be patient with me maybe we will go through with this, or if you give me your email it may be better my email is Barbaram1202@yahoo.com.

 

[Akuini]

Sorry I am a beginner at this, so the id numbers consists of the letter in the beginning and then the number, for example:
L-24, S-45, N-65, L-65, S-2, etc...
and the numbers will populate the combobox, so when I click on some number in the combobox then I want the formula to do something and for this part I have a code.

and the numbers will populate the combobox, so when I click on some number in the combobox then I want the formula to do something and for this part I have a code.

OK, so the id number (e.g. L-1,S-2 etc) will populate the combobox1, right? I mean not just the number part (e.g. 1,4,6, etc)
Try this:

tx = Split(ComboBox1.Value, "-")(0)

So if you choose L-10 in the combobox1 then tx value is "L"

Sorry again, I do not know the technical words for VBA, so you will be patient with me maybe we will go through with this, or if you give me your email it may be better my email is Barbaram1202@yahoo.com.

Sorry, all discussion should be done in this forum, it's part of Forum rules.
But it will be helpful if you can upload your sample workbook (without sensitive data) somewhere (maybe to dropbox.com or google drive) then post the link here.

 

[JarekM]

[/URL][/IMG]



So the code works really well and I changed it to this:


tx = Split(ComboBox1.Value, "-")(0)


If ComboBox1.Value = "L" Then
MsgBox "Exit ", vbInformation

ElseIf ComboBox1.Value = "S" Then
MsgBox "Do you want save this Invoice ", vbInformation

ElseIf ComboBox1.Value = "R" Then
MsgBox "This is return Invoice ", vbInformation
End If

ComboBox1 = tx

End Sub



I think we are really close, when I click for example S-11 like in the picture, the code does what we want, which shows the message box, but the problem is that the combobox only populates the letter S and I want the combobox to show the whole ID number, so the letter and the number, and still show the message box. Everything else is fine and does what we want. The message box shows up for the specific letter I click that is in the code.


Thank you

 

[Akuini]

I think we are really close, when I click for example S-11 like in the picture, the code does what we want, which shows the message box, but the problem is that the combobox only populates the letter S and I want the combobox to show the whole ID number, so the letter and the number, and still show the message box. Everything else is fine and does what we want. The message box shows up for the specific letter I click that is in the code.

Try this:

tx = Split(ComboBox1.Value, "-")(0)


If tx = "L" Then
MsgBox "Exit ", vbInformation

ElseIf tx = "S" Then
MsgBox "Do you want save this Invoice ", vbInformation

ElseIf tx = "R" Then
MsgBox "This is return Invoice ", vbInformation
End If

[COLOR=#ff0000]'ComboBox1 = tx[/COLOR]  'don't use this line

 

[JarekM]

Thank you, this is exactly what I wanted. Thank you for all of your work and being patient with me.
If I am going to need any help in the near future, will you be able to help me and if so is there any way I can contact you.
I am working on an project and I will need sometimes bigger codes.

If not
Thanks again.

 

[Akuini]

Thank you, this is exactly what I wanted. Thank you for all of your work and being patient with me.
If I am going to need any help in the near future, will you be able to help me and if so is there any way I can contact you.
I am working on an project and I will need sometimes bigger codes.

If not
Thanks again.

You’re welcome.
Well, you can always start a new thread for your new problem then you can contact me by PM to inform me about the new thread.