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.

 

[kelly mort]

Okay. I appreciate your time

Have a nice time

PS.

What if

RNG.cells(1,1) is blank ,

How do we locate the none blank cell for V1?

 

[kelly mort]

Okay. I appreciate your time

Have a nice time

PS.

What if

RNG.cells(1,1) is blank ,

How do we locate the none blank cell for V1?

I have fixed this with the loop setting the condition to

If cel <>"" then


Now this is what just came up - I over looked it previously.
.

If the are items in CB that have no value in CE then I will like to display those items under those with value as:


Item 1
Item 2
Item n

When n is the maximum number of items with no value.

 

[Yongle]

Will update thread when back at PC tomorrow

 

[kelly mort]

Will update thread when back at PC tomorrow

Okay

 

[Yongle]

Try this which takes account of blank cells for V1 to V4 and lists the blank cells (if any ) in 2nd message box

    Dim ws As Worksheet, rng As Range, cel As Range, myStr As String
    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))
    
    For Each cel In rng
        If cel > "" Then
            V1 = cel
            Exit For
        End If
    Next
    For Each cel In rng
        If cel <> V1 And cel > "" Then
            V2 = cel
            Exit For
        End If
    Next
    For Each cel In rng
        If cel <> V1 And cel <> V2 And cel > "" Then
            V3 = cel
            Exit For
        End If
    Next
    For Each cel In rng
        If cel <> V1 And cel <> V2 And cel <> V3 And cel > "" Then
            V4 = cel
            Exit For
        End If
    Next
    MsgBox "1: " & V1 & G & "2: " & V2 & G & "3: " & V3 & G & "4: " & V4, , "4 VALUES"
    
    For Each cel In rng
        If cel = "" Then myStr = myStr & vbCr & cel.Address(0, 0)
    Next
    If myStr > "" Then MsgBox myStr, , "EMPTY CELLS"
End Sub

 

[kelly mort]

Very powerful.

I have really learnt so many tricks from you.

Have a nice time

 

[Yongle]

thanks for your feedback