VBA Excel: Getting result for multiple cells

Question

I am trying to get the result of different cells based on a preset if statement I am receiving the result in multiple message boxes, and the result for all cells is being calculated based on the first statement check up. How can i receive all results in a single msgboxand allow the function to check every single cell in the range?

Dim rcell As Range
For Each rcell In Vou_Summary.Range("I5:I16")
    If 0 < rcell <= 2.5 Then
        MsgBox rcell.Cells.Offset(0, -3).Value & " Is Critical", vbOKOnly, "Notice!"
    ElseIf 2.5 < rcell <= 4 Then
        MsgBox rcell.Cells.Offset(0, -3).Value & " Requires PR Placement", vbOKOnly, "Notice!"
    ElseIf rcell >= 7 Then
        MsgBox rcell.Cells.Offset(0, -3).Value & " Is Oversupplied", vbOKOnly, "Notice!"
    Else
End If
Next

Community · Accepted Answer · 2017-04-13 12:40:36Z

3

You can create a String variable first. Don't throw a MsgBox but save the results into the variable. Past the End If statement show the MsgBox and pass the string variable as the content.

Better, you can use a String Builder to make it work about 1000 times faster than the default VBA concatenation ;)

edited Apr 13, 2017 at 12:40

CommunityBot

11 silver badge

answered Nov 19, 2014 at 13:43

user2140173

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

Collectives™ on Stack Overflow

VBA Excel: Getting result for multiple cells

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest