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.
 
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?
 
Last edited:
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
Will update thread when back at PC tomorrow
 
Upvote 0
Try this which takes account of blank cells for V1 to V4 and lists the blank cells (if any ) in 2nd message box

Code:
    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
 
Last edited:
Upvote 0
Very powerful.

I have really learnt so many tricks from you.

Have a nice time
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
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