Excel Display variable data in msgbox

Question

There are two columns. one has some names of tables and second one has its values.

Dim rng As Range
    Set rng = [AH3:AH50]  ' <-- adjust to your requirements    
    If Application.WorksheetFunction.Max(rng) > 95 Then    
    MsgBox "tables has sixe more than 95"    
End If

Help required is:
1. Msgbox should also display the actual value of the cell it finds more than 95.
2. Also it should display the name of the corresponding table for which it is displaying the value in step one.

msgbox output should be:
"ABCD table has current size:96.6"

Jamie Bull · Accepted Answer · 2012-10-09 08:36:24Z

2

This code assumes the table names are in the column before the table values. Change the Offset parameter from -1 if that's not the case.

Sub TestRange()
    Dim rngValues As Range
    Dim strTableName As String
    Dim cell As Range

    ' Adjust ranges to suit
    Set rngValues = [C2:C8]

    For Each cell In rngValues
        If cell.Value > 95 Then
            MsgBox Range(cell.Address).Offset(0, -1).Value _
                & " table has current size: " & cell.Value
        End If
    Next
End Sub

answered Oct 9, 2012 at 8:36

Jamie Bull

13.6k18 gold badges80 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1728174 Over a year ago

This worked absolutely fine. Thanks Jamie. Effort Appreciated.

user1728174 Over a year ago

Hey Jamie...Need more help from you.

chris neilsen · Accepted Answer · 2012-10-09 08:18:54Z

0

Try this

Updated to find all instances > threshold

Sub TestRange()
    Dim rngValues As Range
    Dim rngTables As Range
    Dim mxVal As Variant
    Dim idx As Long

    ' Adjust ranges to suit
    Set rngValues = [F1:F10]
    Set rngTables = [E1:E10]
    mxVal = Application.WorksheetFunction.Max(rngValues)
    Do While mxVal > 95
        idx = Application.Match(mxVal, rngValues, 0)
        MsgBox Application.Index(rngTables, idx) & " table has current size: " & mxVal

        If idx < rngValues.Rows.count Then
            Set rngValues = rngValues.Offset(idx, 0).Resize(rngValues.Rows.count - idx)
            Set rngTables = rngTables.Offset(idx, 0).Resize(rngTables.Rows.count - idx)
            mxVal = Application.WorksheetFunction.Max(rngValues)
        Else
            mxVal = 0
        End If
    Loop
End Sub

edited Oct 9, 2012 at 8:18

answered Oct 9, 2012 at 5:19

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

7 Comments

user1728174 Over a year ago

Chris, again a nice code but it does not fulfill my requirement. What it is doing is: it finds the maximum value in the entire column and if it is above 95 it shows me the msgbox once. But i want that code should display the msgbox equal times as much there are values above 95. For e.g there are 3 values in the entire column above 95, it should display the msgbox three times.

chris neilsen Over a year ago

@Jamie Bull - if you have a different solution, post your own answer. Don't vandalise mine

Jamie Bull Over a year ago

@chris neilsen My apologies. I thought that was the point of a wiki. Added my own.

chris neilsen Over a year ago

@Jamie Bull That ok, we live and learn. Editing an answer to improve it is fine. IMHO changing it to a completely different answer is not.

user1728174 Over a year ago

Sorry but it gave me the same output as before..i.e msgbox displayed only once.

|

Collectives™ on Stack Overflow

Excel Display variable data in msgbox

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related