Message box display of grouped items from a range.

[kelly mort]

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.

 

[Logit]

.
https://www.excel-easy.com/vba/msgbox.html

 

[Rajneesh Rawat]

Hi,

Hope this help.

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]

.
https://www.excel-easy.com/vba/msgbox.html

Hi @Logit,

I am cool with how to shoot the various MsgBox .

What I am stacked with is how get the list items divided into four, with the values assigned as I have stated above.

 

[kelly mort]

Hi,

Hope this help.

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]

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]

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]

try this

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

 

[kelly mort]

Sure!!!!

This is more than I am looking for.



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]

expand each condition with

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