Message box display of grouped items from a range.

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
In column CB I have list of items then CE has their respective values.

Now the items in CB are grouped into four - meaning each group has same value.

So I need a way to display with a message box - VBA:

Group 1 = Value 1
Group 2 = Value 2
Group 3 = Value 3
Group 4 = Value 4

I have been thinking about it since yesterday . no success yet so I decided to call for tech support.


There are at most 14 individual items present in column CB. And same character length.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi,

Hope this help.
Code:
Sub Qry()

MsgBox Range("Cb1") & " = " & Range("CE1") & vbNewLine _
& Range("Cb2") & " = " & Range("CE2") & vbNewLine _
& Range("Cb3") & " = " & Range("CE3") & vbNewLine _
& Range("Cb4") & " = " & Range("CE4") & vbNewLine _


End Sub
 
Upvote 0
Hi,

Hope this help.
Code:
Sub Qry()

MsgBox Range("Cb1") & " = " & Range("CE1") & vbNewLine _
& Range("Cb2") & " = " & Range("CE2") & vbNewLine _
& Range("Cb3") & " = " & Range("CE3") & vbNewLine _
& Range("Cb4") & " = " & Range("CE4") & vbNewLine _


End Sub


Okay let me be clear here:

The database is a bit long about 1000 rows with multiples of same item. For example there could be say 20 or more of "item x". Before "item y" in that order.


So it is those items that I want to group into the four groups.

Say group 1 could be made up of "item x", " item y" and "item z"

And any of these items x y z could be as many as say 20 or more as stated above.

So in case they ,x y z , have they value of 10, then the message box should look like:

Group 1 = 10


I hope this clarifies things a bit.
 
Upvote 0
:confused:
Are you saying
- there are only 4 possible values in column CE
- each item in CB is in the same "group" as every other item in CB sharing the same value in CE
 
Upvote 0
:confused:
Are you saying
- there are only 4 possible values in column CE
- each item in CB is in the same "group" as every other item in CB sharing the same value in CE

Yes to all questions
 
Upvote 0
try this

Code:
Sub Get4Values()
    Dim ws As Worksheet, rng As Range, cel As Range
    Dim V1 As String, V2 As String, V3 As String, V4 As String
    Const G = vbCr
    Set ws = ActiveSheet
    Set rng = ws.Range("CE2", ws.Range("CE" & ws.Rows.Count).End(xlUp))
    
    V1 = rng.Cells(1, 1)
    
    For Each cel In rng
        If cel <> V1 Then
            V2 = cel
            Exit For
        End If
    Next
    For Each cel In rng
        If cel <> V1 And cel <> V2 Then
            V3 = cel
            Exit For
        End If
    Next
    For Each cel In rng
        If cel <> V1 And cel <> V2 And cel <> V3 Then
            V4 = cel
            Exit For
        End If
    Next
    MsgBox "1: " & V1 & G & "2: " & V2 & G & "3: " & V3 & G & "4: " & V4
    
End Sub


Assumes values begin in CE2 and that there are no empty cells
 
Last edited:
Upvote 0
Sure!!!!

This is more than I am looking for.

:cool:

I would have spent millennia trying to figure it out.

So for future sake, how do I take care of any possible blanks in CE?

that's when there is a blank we don't include it.
 
Upvote 0
expand each condition with

Code:
IF .... [COLOR=#ff0000]And cel <>""[/COLOR] Then
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top