Message box display of grouped items from a range.

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
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.
 

Rajneesh Rawat

New Member
Joined
Mar 31, 2017
Messages
32
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
Windows
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,891
Office Version
365
Platform
Windows
: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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
Windows
: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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,891
Office Version
365
Platform
Windows
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:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
Windows
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,891
Office Version
365
Platform
Windows
expand each condition with

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

Forum statistics

Threads
1,085,169
Messages
5,382,116
Members
401,771
Latest member
Polarak

Some videos you may like

This Week's Hot Topics

Top